Copy data till the last row from one sheet and paste in the last column available of the another sheet.

[Harshil Mehta]

Expectation: I want to copy data from sheet7.Range G10 till the last row and paste in sheet4, Row No.10, last column available.

Problem: The below code does not copy or paste (no error is shown by the debugger). I dont understand where I am going wrong.

Could anyone please help me?

Sub cf()

Dim lr, lcol1 As Long

With Sheet7


lr = .Cells(.Rows.Count, 7).End(xlUp).Row
lcol1 = Sheet4.Cells(10, Columns.Count).End(xlToLeft).Column + 1

.Range("G10" & lr).Copy

Sheet4.Cells(10, lcol1).PasteSpecial



End With
End Sub

 

[davesexcel]

You are missing part of the range to copy, you have G10 & lr, if lr was 20 then G1020 would be copied.

Sub cf()
    Dim sh As Worksheet, ws As Worksheet
    Dim lr As Long, lcol1 As Long

    Set sh = Sheet7
    Set ws = Sheet4

    With sh
        lr = .Cells(.Rows.Count, 7).End(xlUp).Row
        lcol1 = ws.Cells(10, ws.Columns.Count).End(xlToLeft).Column + 1
        .Range("G10:G" & lr).Copy
        ws.Cells(10, lcol1).PasteSpecial
    End With

End Sub

 

[Trebor76]

Hi there,

Try this:

Option Explicit
Sub cf()

    Dim lr As Long, lcol1 As Long
    
    With Sheet7
        lr = .Cells(.Rows.Count, 7).End(xlUp).Row 'Finds the last row in Col. G of sheet code name 'Sheet7'
        lcol1 = Sheet4.Cells(10, Columns.Count).End(xlToLeft).Column + 1 'Finds the last column across row 10 in sheet code name 'Sheet4' and increments by one
        .Range("G10:G" & lr).Copy 'assumes last row is equal to or more than 10
        Sheet4.Cells(10, lcol1).PasteSpecial
        Application.CutCopyMode = False
    End With

End Sub

Regards,

Robert

 

[Harshil Mehta]

You are missing part of the range to copy, you have G10 & lr, if lr was 20 then G1020 would be copied.

Sub cf()
    Dim sh As Worksheet, ws As Worksheet
    Dim lr As Long, lcol1 As Long

    Set sh = Sheet7
    Set ws = Sheet4

    With sh
        lr = .Cells(.Rows.Count, 7).End(xlUp).Row
        lcol1 = ws.Cells(10, ws.Columns.Count).End(xlToLeft).Column + 1
        .Range("G10:G" & lr).Copy
        ws.Cells(10, lcol1).PasteSpecial
    End With

End Sub

Thank you so much for your help.