Amending a formula

[leerem]

could you please advise on how I can ammend the following formula to enable
me to copy data from a different worksheet, eg. to copy data from Sheet1 to
sheet2 ensuring only the data values displayed are copied

Lastrow = Cells(Rows.Count, "CA").End(xlUp).Row
Range("CA10:CA" & Lastrow).Copy
Range("CH10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

many thanks

 

[Stefi]

Lastrow = Worksheets("Sheet1").Cells(Rows.Count, "CA").End(xlUp).Row
Worksheets("Sheet1").Range("CA10:CA" & Lastrow).Copy
Worksheets("Sheet2").Range("CH10").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Regards,
Stefi

„leerem†ezt írta:

 

[Per Jessen]

Hi

This will copy non-blank cells to sheet2:

Lastrow = Cells(Rows.Count, "CA").End(xlUp).Row
Range("CA10:CA" & Lastrow).Select
Selection.AutoFilter Field:=1, Criteria1:="<>"
Selection.Copy Destination:=Sheets("Sheet2").Range("CH10")
Selection.AutoFilter

Best regards,
Per

 

[Don Guillett]

Another way if the ranges are the same size

with sheets("sheet1")
Lastrow = .Cells(Rows.Count, "CA").End(xlUp).Row
sheets("sheet2").Range("CH10:ch" & lastrow).value= _
.Range("CA10:CA" & Lastrow).value
end with