Code check

[Miya]

Hi, how can i use paste values in the below code when copying data to another sheet, i get an error when using the below.

Sub test()
Dim LR As Long
With Sheets("Rec")
LR = .Cells(Rows.Count, 1).End(xlUp).Row
    With .Range("E8:E" & LR)
        .Copy Destination:=Sheets("Data").Range("A9")
        .PasteSpecial xlPasteValues
    End With
End With
    
End Sub

 

[jeffreybrown]

Hi Miya,

This seems to work for me...

Sub test()
Dim LR As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
Sheets("Rec").Range("E8:E" & LR).Copy Destination:=Sheets("Data").Range("A9")
End Sub

 

[Miya]

Hi Miya,

This seems to work for me...

Sub test()
Dim LR As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
Sheets("Rec").Range("E8:E" & LR).Copy Destination:=Sheets("Data").Range("A9")
End Sub

But if i have columns in the active worksheet which are formatted green then wouldn't the code copy the format over to sheet data?

 

[jeffreybrown]

Yes you are right...let me see if I can figure that one out.

 

[jeffreybrown]

Okay...how about this...

Sub test()
Dim LR As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
Sheets("Rec").Range("E8:E" & LR).Copy
With Sheets("Data").Range("A9")
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
End Sub