VBA Copy & Paste (with last row function)

[mtjanousek]

Hello,
I am trying to copy & paste values in column A from sheet 2 to sheet 3 and I want to copy all values starting with A2 till the end.

I was looking for a solution but without much success. I assume that the mistake is in selection of sheets. So far I have this:

Sub ExampleTest()

    Dim lr As Long
    Application.ScreenUpdating = False
    
'   Find the last used row in Column A of Sheet 2
    With Worksheets("Sheet2")
        lr = .Cells(Rows.Count, "A").End(xlUp).Row
    End With
    
        Sheets("Sheet2").Range("A2:A" & lr).Value = Sheets("Sheet3").Range("A2:A").Value
        
End Sub

 

[footoo]

Sub ExampleTest()
Dim lr As Long
With Worksheets("Sheet2")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    Sheets("Sheet3").Range("A2:A" & lr).Value = .Range("A2:A" & lr)
End With
End Sub

 

[mtjanousek]

Thanks, I already tried something similar before but the problem is that nothing happens, Sheet3 stays empty.

 

[footoo]

Have you tried the code I posted?

 

[mtjanousek]

Yes, I copy pasted your code. I also tried another excel workbook wherein sheet 2 I wrote random values in column A starting with the second row, then applied the macro. Sheet 3 stayed empty.

 

[footoo]

Sub ExampleTest()
Dim lr As Long
With Worksheets("Sheet2")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    Sheets("Sheet3").Range("A2:A" & lr).Value = .Range("A2:A" & lr).Value
End With
End Sub

 

[mtjanousek]

Oh, yes, of course. Thanks a lot!

 

[fbacchus]

This is great. Quick question, let's say I want to copy Column C in Sheet2 and place in Column A in Sheet3, how can I accommodate this with the above code? Thanks.

frank

 

[footoo]

This is great. Quick question, let's say I want to copy Column C in Sheet2 and place in Column A in Sheet3, how can I accommodate this with the above code? Thanks.

frank

Sub ExampleTest()
Dim lr As Long
With Worksheets("Sheet2")
    lr = .Cells(Rows.Count, [COLOR=#ff0000]"C"[/COLOR]).End(xlUp).Row
    Sheets("Sheet3").Range("A2:A" & lr).Value = .Range([COLOR=#ff0000]"C2:C"[/COLOR] & lr).Value
End With
End Sub

 

[fbacchus]

Awesome! Thanks.