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

Harshil Mehta

Board Regular
Joined
May 14, 2020
Messages
55
Office Version
  1. 2013
Platform
  1. Windows
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?

VBA Code:
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,025
You are missing part of the range to copy, you have G10 & lr, if lr was 20 then G1020 would be copied.
VBA Code:
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

Well-known Member
Joined
Jul 23, 2007
Messages
4,678
Hi there,

Try this:

VBA Code:
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

Board Regular
Joined
May 14, 2020
Messages
55
Office Version
  1. 2013
Platform
  1. Windows
You are missing part of the range to copy, you have G10 & lr, if lr was 20 then G1020 would be copied.
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,865
Messages
5,542,956
Members
410,579
Latest member
bdubz
Top