Paste cells to another worksheet in VBA

Question

I want to paste cells across worksheets in VBA. In the code below, I first select the range of cells, and then paste to another worksheet. But it runs error '9": Subscript out of range. I think the problem is in the last line for copy & paste. Here's my code:

Sub MatchFRB()
' find last row and column 
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
Set StartCell = Range("A1")
LastRow = Sheet22.Cells(Sheet22.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = Sheet22.Cells(StartCell.Row, Sheet22.Columns.Count).End(xlToLeft).Column

' Select cells until meets Threshold=5000000000
Dim i As Integer
Dim Bal As Double
Threshold = 0

For i = 2 To LastRow
Bal = Threshold + Range("AV" & i)
If Threshold > 5000000000# Then  
    Exit For
End If
    Next i 

' copy cells from Sheet22 and paste to Sheet21
Sheet22.Range(StartCell, Sheet22.Cells(i, LastColumn)).Copy Worksheets("Sheet21").Range(StartCell, Sheet21.Cells(i, LastColumn))

End Sub

Many thanks!

Just a comment, it's a little confusing when you switch conventions for the worksheet. Sheet22.Range() vs Worksheets("Sheet21"). I'd pick one way and stick with that. That being said, which line throws the error? (Click "Debug" when the error pops up). Also, are the origin and destination ranges the same size? — BruceWayne
Set StartCell = Range("A1") I'd fully qualify this line then try again - Set StartCell = Sheet22.Range("A1") — dwirony
Hello and welcome to Stack Overflow! This subject has been thoroughly discussed across several other posts on this website. Make sure you are setting the Range object on your destination sheet appropriately. Also, the step-by-step debugger on VBA can help you find the error. — fabio.avigo

Jchang43 Jchang43 · Accepted Answer · 2018-10-02T21:30:57

You have to properly call to your sheet. VBA doesn't accept just the name of the sheet as an object. You have to reference to the sheet with Worksheets("Sheet22"), another option would be to set an object to be this:

Dim ws as object
set ws = Thisworkbook.Worksheets("Sheet22")

This way VBA knows you want Sheet22 from the book that the macro is in; otherwise you could specify the workbook with Workbooks("YourWorkBookName").WorkSheets("SheetName").

From there you could use ws.Range as you were doing with Sheet22. Similarly, StartCell may be a range, but it only acts with the active sheet, so it wouldn't be a bad idea to reference it to a certain sheet and/or book as well. But in this case, I've left it out because it's always A1 and that's simple enough to enter.

Later in your code when you're trying to calculate the balance, you also have to use .Value after you call your range so that you actually access the number stored in the cell. But if you're threshold is what you're checking you should be adding the threshold back to itself. However, I've chosen to just use Bal in this case because it made more sense to me.

Sub MatchFRB()
' find last row and column 
Dim LastRow As Long
Dim LastColumn As Long
Dim ws as object
Dim i As Integer
Dim Bal As Double

set ws = Thisworkbook.Worksheets("Sheet22")

LastRow = ws.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
LastColumn = ws.Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column

' Select cells until meets Threshold=5000000000

Bal = 0

For i = 2 To LastRow
    Bal = Bal + ws.Range("AV" & i).Value
    If Bal >= 5000000000 Then  
        Exit For
    End If
Next i 

' copy cells from Sheet22 and paste to Sheet21
ws.Range("A1:" & Cells(i, LastColumn).Address).Copy Worksheets("Sheet21").Range("A1:", Cells(i, LastColumn).address)

End Sub

Paste cells to another worksheet in VBA

1 Answers