Free Microsoft Excel 2013 Quick Reference

Prevent copy and paste merged cells in protected Excel 2000

I have created a series of Excel 2000 worksheets for data entry. I've protected the worksheet to prevent user from changing formulas. Some of the cells used in data entry are merged. If the user selects a merge cell, then copies it and pastes it to a non-merged cell, the non-merged cell becomes a merged cell, thus screwing up data entry in the newly merged cell. Is there any way to prevent this?

Thanks for your reply.
Tommy


I've written this code to copy and paste a cell based on certain criteria. It works very well. However, instead of copying and pasting one cell, in this case Range("C" & b), I want to copy and paste Range("B" & b) and Range("C" & b) concatenated. The first range contains a first name and the second range contains a surname. I want the full name to appear in one cell in Column Q. I've tried everything but I can't seem to get anywhere. Any help would be great!


	VB:
	
Dim lCount As Long 
Dim BirdieHole As Range 
Set BirdieHole = Range("E4") 
For lCount = 1 To WorksheetFunction.CountIf(Rows(4), RandNum) 
    Set BirdieHole = Rows(4).Find(What:=RandNum, After:=BirdieHole, _ 
    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _ 
    SearchDirection:=xlNext, MatchCase:=False) 
    If BirdieHole = RandNum Then Exit For 
Next lCount 
 
Dim BirdieHoleCell As Range 
Set BirdieHoleCell = Cells(5, BirdieHole.Column) 
Dim bottomQ As Long 
bottomQ = Range("q" & Rows.Count).End(xlUp).Row 
Range("Q23" & ":Q" & bottomQ).Delete 
Dim b As Integer 
For b = 7 To bottomQ 
    Dim Par As Integer 
    Par = Right(BirdieHoleCell, 1) 
    If Cells(b, BirdieHole.Column).Value < Par Then 
        Range("C" & b).Copy _ 
        Destination:=Range("Q65536").End(xlUp).Offset(1, 0) 
    End If 
Next b 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi...

I am attempting some copy and pasting of cells in a large worksheet ( attached ).

Basically on the settings' page I set the year using the slider feature and
the calender is created in the calender sheet and then copied to the various months of the year.

The row height and column width have been checked and are idenitcle across all the pages.

When I run the code I get an error ( RUN TIME ERROR 1004 ) which basically says that this operation requires the cells be identically sized. This line of the VB code is highlited.

Worksheets(strPageName).Range("A5").PasteSpecial Paste:=xlPasteValues

I have checked all the cells and cannot see the problem.

Can anyone help ?

Thanks to JBeaucaire the orignal thread "Copy and Paste till last Last Column".

Once I applied this to the macro I then added the following:

Range("K3").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[221]C)"
    Range("L3").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(2,R[2]C:R[221]C)"
    Range("K3:L3").Select 
LastCol = Cells(4, Columns.Count).End(xlToLeft).Column
    Range("K3:L3").Copy
    Range("M3", Cells(3, LastCol)).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
So now my entire code looks like this:

Option Explicit

Sub CleanItA()
Dim LastCol As Long

    Rows("4:4").Select
     Selection.UnMerge
      Rows("6:6").Select
       Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    LastCol = Cells(5, Columns.Count).End(xlToLeft).Column
     Range("A6", Cells(6, LastCol)).FormulaR1C1 = _
        "=IF(ISBLANK(R[-2]C),R[-1]C,CONCATENATE(R[-2]C,"" "",R[-1]C))"
    
    Range("A6", Cells(6, LastCol)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Rows("4:5").Select
     Range("A5").Activate
       Selection.Delete Shift:=xlUp
    
    Range("A4:Z4").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 16751001
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    Rows("3:3").Select
     Selection.UnMerge
    
    Range("K3").Select
     ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[221]C)"
    Range("L3").Select
     ActiveCell.FormulaR1C1 = "=SUBTOTAL(2,R[2]C:R[221]C)"
    
    LastCol = Cells(4, Columns.Count).End(xlToLeft).Column
    Range("K3:L3").Copy
    Range("M3", Cells(3, LastCol)).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

End Sub

That didn't work. I assumed it has to to with the two different LastCol lines of code I have so I went and did the following:

Option Explicit

Sub CleanItB()
Dim LastColA As Long
Dim LastColB As Long

    Rows("4:4").Select
     Selection.UnMerge
      Rows("6:6").Select
       Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    LastColA = Cells(5, Columns.Count).End(xlToLeft).Column
     Range("A6", Cells(6, LastColA)).FormulaR1C1 = _
        "=IF(ISBLANK(R[-2]C),R[-1]C,CONCATENATE(R[-2]C,"" "",R[-1]C))"
    
    Range("A6", Cells(6, LastColA)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Rows("4:5").Select
     Range("A5").Activate
       Selection.Delete Shift:=xlUp
    
    Range("A4:Z4").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 16751001
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    Rows("3:3").Select
     Selection.UnMerge
    
    Range("K3").Select
     ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[221]C)"
    Range("L3").Select
     ActiveCell.FormulaR1C1 = "=SUBTOTAL(2,R[2]C:R[221]C)"
    
    LastColB = Cells(4, Columns.Count).End(xlToLeft).Column
    Range("K3:L3").Copy
    Range("M3", Cells(3, LastColB)).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

End Sub
That didn't work so I am now attaching a sample work book with the two macros listed above stored in Module 1 and Module 2 respectively. Module 5 has all the individual commands Ive used to get to this point.

The result i am looking for is to copy the formulas in K3 and L3, which are different from one another, and paste across Row 3 starting in M and ending at the last column with data based on Row 4.

Any help would be greatly appreciated.

I am working on spreasheet with 9000 lines. I need a shortcut to copy and
paste same formula in all cells.

Hi Everyone,

Can I copy and paste a cell but add text onto it?

If I have 'code1' in a cell and in 2 the cells beneath I want to have 'code1A' and 'code 1B' can I do this with a formula?

I need this because I have about 1000 product codes that I need to add the same two letters to the end of.

Any help would be greatly appreciated.

Cheers,

Smokey

Hi,

I need help creating a macro that will search a given values of e.g."servername" in the entire worksheet "serverlist" and will just copy and paste onlyspecific column matching the given "servername' e.g.
"serial no.";
"environment";
"platform"
from that worksheet"serverlist" into a new worksheet "updated serverlist".

attached is my excel file.
I am checking 5000 entries and it will take me the whole day to search one my one.

Any help is highly appreciated.

Thanks!

So I've had to Concatenate two rows of cells to combine words. I then want to take this list of cells and move it to another spot, but every time a try to copy and paste the cells re-separate. I've even tried to copy to notebook then move back to excel but the cells keep seperating. Is there any way to keep the cells separated when copy and pasting concatenated cells?

Thanks in advance.

I have created a sheet where I can enter data in B5:B24 called Data Entry. I then click on the button to have this data paste into my "Order Tracking" sheet. This is working perfectly, but the problem I am running into is with my Order Tracking Sheet. The first 11 entries (B4:B13) are being entered into the correct cells because they go from A5:K5, but the other entries are following consecutively but I want them in different locations on the Order Tracking. B14 should show up in Q5, B15 I want in R5, B16 in S5, B17 in T5, B18 in U5, B19 in V5, B20 in W5, B21 in X5, B22 in Y5, B23 in Z5, B24 in AA5. I also want to create this so that anytime I add something from my data entry sheet it will copy and paste the cells in the next row below the existing data in the Order Tracking Sheet which is indicated in the code. Here is what I have:

Code:
Sub UpdateOrderTrackingWorksheet()

    Dim historyWks As Worksheet
    Dim inputWks As Worksheet

    Dim nextRow As Long
    Dim oCol As Long

    Dim myRng As Range
    Dim myCopy As String
    Dim myCell As Range
    
    'cells to copy from Input sheet - some contain formulas
    myCopy = "B5:B24"

    Set inputWks = Worksheets("Data Entry")
    Set historyWks = Worksheets("Order Tracking")

    With historyWks
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
    End With

    With inputWks
        Set myRng = .Range(myCopy)

        If Application.CountA(myRng)  myRng.Cells.Count Then
            MsgBox "Please fill in all the cells!"
            Exit Sub
        End If
    End With

    With historyWks
        With .Cells(nextRow, "A")
            .Value = Now
            .NumberFormat = "mm/dd/yyyy hh:mm:ss"
        End With
        .Cells(nextRow, "B").Value = Application.UserName
        oCol = 3
        For Each myCell In myRng.Cells
            historyWks.Cells(nextRow, oCol).Value = myCell.Value
            oCol = oCol + 1
        Next myCell
    End With
    
    'clear input cells that contain constants
    With inputWks
      On Error Resume Next
         With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.GoTo .Cells(1) ', Scroll:=True
         End With
      On Error GoTo 0
    End With
End Sub

Thanks

Hello,
I am currently using Excel 2002 sp3.
1) I launched an instance of Excel, opened an exising workbook containing
some sample data and a chart.
2) I then launched a second instance of Excel
3) I copied all the contents of the sheet from the first instance of Excel
(including the data and chart) and tried to paste them into a second instance
of Excel.

I noticed that only the data gets pasted but not the chart. Now, if I copy
and paste between workbooks in the same instance, the chart also gets pasted.
So, the question is, is this working as designed or an Excel bug? If so, is
there any way to get this to work?

The reason I am asking is because we have a VB6 application that hosts the
Microsoft DSO Framer control. The DSO Framer control allows the user to open
an Excel workbook. Similarly I followed the below steps
1) I launched my VB6 application and opened an Excel workbook
2) I then launched an external instance of Excel outside of the application,
copied the data and chart and attempted to paste them into the excel instance
within my application.
3) Only the data keeps getting pasted.

The only way for me to paste the chart as well is using Paste Special. Is
there any explanation to this? Thanks!
--
ANeelima

Hi

I have a shared protected workbook into which comments and data are input on
a daily basis to merged cells.

In some cases the same information just has to be copied and pasted into a
new position in the same worksheet. When the cell is copied and pasted the
cell de-merges in the new position. Is there anyway of preventing this?

The only alternative I can currently see is to copy or cut the text in a
cell from the formula bar and then paste this into the new cell. Is this my
only option?

Any advice much appreciated
Dave

Hello all. I have the following code in another workbook that is used to populate a cell on the same sheet based on input to cells in column 'A'.

Is it possible to modify this for the attached workbook to select a cell with data (numbers) on the Input Data sheet in column 'E', add text to the beginning, ('CG' in this case), and paste the result to the Import Template in the corresponding cell of column 'A'? I currently have a formula copied to dozens of cells in 'A' but since the number of rows for the Input Template is variable, there are usually cells in 'A' that contain CG but no corresponding data in the rest of the row.

If there is a better way to code my request, that would be good as well.

Private Sub Worksheet_Change(ByVal Target As Range)

'VBA code to auto populate different protected cells

'ActiveSheet.Protect , UserInterfaceOnly:=True

'Populates Date Added field when Unit field is entered

    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        If Cells(Target.row + 1, "C").Value <> "" Then
            If MsgBox("Order Date already exists. Update the date to today?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
            Cells(Target.row + 1, "C").Value = Date
        Else
            Cells(Target.row + 1, "C").Value = Date
        End If
    End If
End Sub
Thanks in advance!!
Andrew

I got some scripts that prevent copying and pasting.
But they are not preventing:
1) copy and paste in the same cell: this is doing the formatting "merge cell" to go into space.
2) ctrl + d, as this shortcut copy and paste the cell above the cell down behind her with conditional formatting. I need to avoid.
Thanks for you help!

I did some search before I posted this. But they don’t seem to be what I am looking for.

What I am trying to do here is to compare 2 lists in 2 different files, and when there is a match, then copy and paste the related cells of the matching name. Sorry if this sounds messy, perhaps the sample file I have attached can explain better.

Every month I get a new file in the format of “Data Source” sheet where the list of banks in column A and the figures in column M, AA and AB might change from month to month. For the sake of convenience, I put the source data as a different sheet instead of different file here.

I have an existing report template in the format of “Final report” sheet where basically I copy and paste the relevant cells according to the name of the banks.

I don’t think I can use Vlookup because the cells that I want to extract are not right beside the search criteria. If I’m wrong please correct me.

Anyway, assuming a macro is needed for this, I am wondering if I can create a macro, where it can search the list of banks in column A in “Data Source” sheet based on the list in column A in “Final Report” sheet, then copy the correct cells from column M, AA and AB and then paste them into the correct cells in columns B, E and H in “Final Report” worksheet?

Note that not all the banks in the “Final Report” sheet are in the “Data Source”, so for this example, row 4 for ABN Bank should remain blank after the search because it is not listed in the “Data Source”. The Data Source List might also change over time.

There is also this problem of the bank names from the “Data Source” sheet not being exactly the same as the existing list in “Final Report”. For example in this file, ANZ Bank in the other sheet have all the extra stuff behind, but we know it is the same bank. Can the macro solve this issue?

Thanks a lot and hope to get some help soon

Hi all,

I need help with a macro for copying and pasting of cells. I believe this should not be a problem for the Excel VBA experts, but for someone who can only record macro, I'm really at a loss.

Attached is a sample file, where sheet 'Source' is an example of the sheet from which data are to be copied. The other sheet, sheet 'Final' is an example of the final format that I need. The reason I'm doing this is I'm planning to upload my data into Access and so I need to convert them into a list format.

List of target columns in sheet 'Final' and source cells in sheet 'Source':

Column A: Biz ID - not sure if I really need this, by right it should be listed automatically once I paste the data
Column B: B2 of 'Source'
Column C: B2 of 'Source'
Column D: B1 of 'Source'
Column E: row 6, relevant column
column F: column K
column G: row 5, relevant column
column H: the specific amount

So basically I'm creating an entry for every amount in the table.

Hope to hear from someone.. and thanks for your help!

Hi,

I have some master data in sheet2.

In sheet 1 B25 I have some values ( say 100)
in row 28 I have the header( same header is thre in sheet 2)
From A29, to K33 ( 5 rows after the header) I l keep the cells for pasting.

If the value in B25, is greater than 0 ( that is positive value) then sort the field 11 in sheet2 by Descending order , and filter
field 4 By Criteria = "A" and
field 5 By Criteria = "22" and
field 6 By Criteria = "1"

If the value in B25, is Less than 0 ( that is negative value) then sort the field 11 in sheet2 by Ascending order , and filter
field 4 By Criteria = "A" and
field 5 By Criteria = "22" and
field 6 By Criteria = "1"

and now copy the first 5 rows ( that is row without the header) and paste the same in sheet1, A29 to K33.

I have to do like this for 50 times with difference criterias. If I get code for the above i can amend the code and get my work done.

Appreciate your help.
Arvind.

I would love some help on this question

I have a spreadsheet template, this template is filled out and saved under different names in the same location on a shared drive.

From this spreadsheet i want to copy approximately 45 different non continuous cells and paste them into a sheet called Cessations data source. The macro is on the sheet called Cessations data source.

This data source is then used in a mail merge so I need to copy and paste values only to protect my formating.

I have two problems:
1. I need to be able to open and copy information from files that i wont know the name of (I would like to be able to choose the file)
2. I would like the macro to find the last row and paste it into that cells in that row.

Here is what I have this is based on the name of the template and i have specified the cells as row downloading to row 3

Range("A3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R2C13"
Range("B3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R6C2"
Range("D3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R5C2"
Range("E3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R5C6"
Range("H3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R1C13"
Range("K3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R26C2"
Range("L3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R9C6"
Range("M3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R26C5"
Range("N3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R35C2"
Range("O3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R35C5"
Range("P3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R28C5"
Range("Q3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R30C5"
Range("R3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R31C5"
Range("S3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R31C6"
Range("T3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R31C7"
Range("U3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R33C5"
Range("V3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R41C2"
Range("W3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R41C5"
Range("X3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R41C6"
Range("Y3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R41C7"
Range("Z3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R47C3"
Range("AA3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R47C5"
Range("AB3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R46C3"
Range("AC3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R46C5"
Range("AD3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R48C5"
Range("AE3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R48C6"
Range("AF3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R48C7"
Range("AG3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R51C3"
Range("AH3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R51C5"
Range("AI3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R50C3"
Range("AJ3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R50C5"
Range("AK3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R52C5"
Range("AL3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R52C6"
Range("AM3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R52C7"
Range("AN3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R55C3"
Range("AO3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R55C5"
Range("AP3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R54C3"
Range("AQ3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R54C5"
Range("AR3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R56C5"
Range("AS3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R56C6"
Range("AT3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R56C7"
Range("AU3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R65C5"
Range("AV3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R65C6"
Range("AW3").Select
ActiveCell.FormulaR1C1 = "='Final Monies.xls'!R65C7"
Range("A3:AY3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A3").Select
End Sub

I know it is long and complecated if anyone can help solve my problem or suggest something more simple it would be much appreciated!

I have an excel file of 7000 rows and 10 columns of sensitive information. But can I do something in VBA or Excel to prevent and copying and pasting the cells to a new file when the file is open by a client? They can print screen is the only way to capture the information.

Hi

I'm running the following code to create a unqiue list of codes on the
previous worksheet.
The codes are listed in cell A8:A1000 on the active worksheet and the list
is created on the previous worksheet in cells A13:A100

It's working good.. except.. I cannot copy and paste any cells contents on
the active worksheet.
I select a cell, select copy.. it copies the cell (dotted lines blink around
the cell) , and when I click out of that cell into another one.. the paste
option is not available and the dotted lines..
I can copy data in a cell on the activeworksheet and paste it to a different
worksheet.. and I can paste data from a different worksheet into a cell on
this activeworksheet.
It's only when I copy data from this sheet and try to paste it to this
sheet.

What is causing this...?? Is there something I can add to the code to
correct this...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Dim prevSheet As Worksheet

With Me
If .Index = 1 Then
MsgBox "No sheets to the left"
Set prevSheet = Worksheets("Adjustments")
Else
Set prevSheet = Worksheets(.Index - 1)
End If

.Unprotect Password:="test"
If Not Application.Intersect(Target, _
Range("A8:A1000")) Is Nothing Then
prevSheet.Unprotect Password:="test"
prevSheet.Range("A13:A100").ClearContents
prevSheet.Unprotect Password:="test"
gCopyUnique Range("A8:A1000"), prevSheet.Range("A13")
End If
.Unprotect Password:="test"
'Range("R16:R51").Select
prevSheet.Unprotect Password:="test"
prevSheet.Range("A13:A47").Sort , _
Key1:=prevSheet.Range("A13"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
.Protect Password:="test", DrawingObjects:=True, _
Contents:=True, Scenarios:=True

End With
prevSheet.Protect Password:="test", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
Application.ScreenUpdating = Ture
End Sub
****************

Public Sub gCopyUnique(rrngSource As Range, rrngDest As Range)

ActiveSheet.Unprotect Password:="test"
rrngSource.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=rrngDest, Unique:=True
ActiveSheet.Protect Password:="test", DrawingObjects:=True, Contents:=True,
Scenarios:=True

End Sub

As always.. Thanks in advance for you help!!
Kimberly

Hi,

I am trying to create a worksheet that is flexible enough so that
additional rows can be created (by copying existing rows from above) if
additional data becomes available to be entered. Locking rows with
formulas and protecting the sheet does allow the user to copy and paste
the cells or rows (most often accomplished by dragging by the handle);
however, the lock is no longer preserved if the destination rows
unlocked to begin with. On the other hand, if the destination rows are
locked, then the user cannot drag (or copy/paste) into them. I had
assumed that copy and paste (or drag) would preserve cell formats,
including teh fact that they are locked, but I guess I was wrong.
Users will be working with either Office 2002 or Office 2003; the
worksheet template is being created in Office 2002.

Thanks for any help you can provide!

Hi. I have 2 worksheets. If I have a value in Cell B1, I would like Excel to look for that same value in another worksheets and then once it finds that value, to copy the cells that are in rows B3:B33 from the original worksheet and paste values into cells below wherever the value is matched in the 2nd worksheet.

so if B1 contained "BreadPudding" and that same value was found in AZ4 of the destination worksheet, then it would copy B3:B33 from the original worksheet and paste them into AZ6:AZ36 of the destination worksheet.

There are altogether 10 columns each on 6 different tabs that need to be copied and pasted from the original worksheet, always located between rows 3:33. The destination workbook has 12 tabs, and the matching data could be in row 6 of any of the columns in any of the tabs. The data would always be pasted in rows 6:26.

Any ideas how to write a VBA code that will automate this copy and paste values process?
Thanks,

Noob here. Forgive me if this is elsewhere but I've searched for several hours with no luck.

What I'd like to do is this: If Cell F1 contains the word "Yes", I would like the content from cells A1:E1 to be copied and pasted into cells H1:L1. This would apply down the rows, so that if cell F2 contained the word "yes", content from cells A2:E2 would be copied and pasted to cells H2:L2, and so forth, stopping at (and thus including) the last row of data in the spreadsheet.

The "no" in column G is a result of the output of survey data. I figure I only need to reference the "yes" in column F to get the data copied and pasted accurately.

Please see attached excel file for reference.

(I'm using Excel 2010 on Windows 7.)

Thanks so much!

Good Morning

Been having some difficulties getting some code to work and would love some assistance.
I have attached a "stripped out" copy of the spreadsheet i'm using currently.

What i require is for any information that is inputted into Column O in "Unpaid Sales" to be then copied and pasted into "Jobs In Production" in the exact same cell reference

e.g.
Someone enters 05/05/2011 in Column O, Row 12 in "Unpaid Sales"
this then is replicated in Column O, Row 12 in "Jobs In Production"

I'm not sure if it will make a difference however upon exiting the spreadsheet all data currently in "Unpaid Sales" will be deleted and "refreshed" next time the spreadsheet is opened (code for this is already included in the spreadsheet attached)

Hi to anyone who may be able to help,

I am trying to produce a wages history page to calculate accruals and YTD's. I have allocated each pay week a line and the weekly figures are sent from payslip sheet. I plan to copy the previous week's line into the current week to pass on the formulas and formats, then copy and paste special the previous week back over itself to archive and protect the values and formats only.

I don't know how to make this copy and paste the NEXT line, or perhaps select a cell/cellgroup to have it relate to with each run.

If I make sense to anybody, could you please respond - my L-plates are dragging dangerously low to the ground...

Hi

I'm running the following code to create a unqiue list of codes on the
previous worksheet.
The codes are listed in cell A8:A1000 on the active worksheet and the list
is created on the previous worksheet in cells A13:A100

It's working good.. except.. I cannot copy and paste any cells contents on
the active worksheet.
I select a cell, select copy.. it copies the cell (dotted lines blink around
the cell) , and when I click out of that cell into another one.. the paste
option is not available and the dotted lines..
I can copy data in a cell on the activeworksheet and paste it to a different
worksheet.. and I can paste data from a different worksheet into a cell on
this activeworksheet.
It's only when I copy data from this sheet and try to paste it to this
sheet.

What is causing this...?? Is there something I can add to the code to
correct this...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Dim prevSheet As Worksheet

With Me
If .Index = 1 Then
MsgBox "No sheets to the left"
Set prevSheet = Worksheets("Adjustments")
Else
Set prevSheet = Worksheets(.Index - 1)
End If

.Unprotect Password:="test"
If Not Application.Intersect(Target, _
Range("A8:A1000")) Is Nothing Then
prevSheet.Unprotect Password:="test"
prevSheet.Range("A13:A100").ClearContents
prevSheet.Unprotect Password:="test"
gCopyUnique Range("A8:A1000"), prevSheet.Range("A13")
End If
.Unprotect Password:="test"
'Range("R16:R51").Select
prevSheet.Unprotect Password:="test"
prevSheet.Range("A13:A47").Sort , _
Key1:=prevSheet.Range("A13"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
.Protect Password:="test", DrawingObjects:=True, _
Contents:=True, Scenarios:=True

End With
prevSheet.Protect Password:="test", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
Application.ScreenUpdating = Ture
End Sub
****************

Public Sub gCopyUnique(rrngSource As Range, rrngDest As Range)

ActiveSheet.Unprotect Password:="test"
rrngSource.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=rrngDest, Unique:=True
ActiveSheet.Protect Password:="test", DrawingObjects:=True, Contents:=True,
Scenarios:=True

End Sub

As always.. Thanks in advance for you help!!
Kimberly