CODEWITHSUNDEEP
vbaexcel
Jain
Jain

Reputation: 999

Copy-offset method not working

Sub BSRange()

Set ws1 = ThisWorkbook.Worksheets("Balance")
Set ws2 = ThisWorkbook.Worksheets("Summary")
Set ws3 = ThisWorkbook.Worksheets("Cash")
Dim Lastcol As Long
Dim Lastrow As Long
Dim colname As String
Lastcol = ws1.Cells(1, ws1.Columns.Count).End(xlToLeft).Column

For i = 2 To Lastcol

  With ws1
    colname = Split(Cells(, i).Address, "$")(1)
    Lastrow = .Cells(.Rows.Count, colname).End(xlUp).Row
  End With

  With ws3
    Range(Cells(4, i), Cells(Lastrow, i)).Copy ws2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 1)
  End With

  With ws1
    Range(Cells(4, i), Cells(Lastrow, i)).Copy ws2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
  End With

Next i
End Sub

The data does not copy and the compiler shows no error in the code. Also, when I try to get rid of With in the For loop, using SheetName in the prefix, then it gives me an error.

Upvotes: 0

Views: 43

Answers (1)

Kyle
Kyle

Reputation: 2545

Try with these edits. I think you just need to be more careful about qualifying worksheets when you are working across multiple. For instance Cell() will call on the active worksheet, .Cells() will call on the workbook qualified in you With statement.

Sub BSRange()
Set ws1 = ThisWorkbook.Worksheets("Balance")
Set ws2 = ThisWorkbook.Worksheets("Summary")
Set ws3 = ThisWorkbook.Worksheets("Cash")
Dim Lastcol As Long
Dim Lastrow As Long
Dim colname As String
Lastcol = ws1.Cells(1, ws1.Columns.Count).End(xlToLeft).Column

For i = 2 To Lastcol

With ws1
colname = Split(.Cells(, i).Address, "$")(1)
Lastrow = .Cells(.Rows.Count, colname).End(xlUp).Row
End With

With ws3
.Range(.Cells(4, i), .Cells(Lastrow, i)).Copy ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Offset(1, 1)
End With

With ws1
.Range(.Cells(4, i), .Cells(Lastrow, i)).Copy ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

Next i
End Sub

Upvotes: 2

Related Questions