VBA copy-paste in a weekly generated report

Question

Currently working on a file that is being updated every 7 days. In the other workbook (let's call it workbook2), the macro does prepare the data to be copied (performs some row/column operations) and pasted to the report (again, workbook1). The problem is that the data is monthly stackable (i.e. in the workbook2, data is being cumulated until the last day of the month).

So that when I copy the whole "prepared range" from workbook2, in order to paste it in workbook1 I look for the specific cell "B1" (month) from workbook2 in workbook1. After finding the first cell in a column containing "B1" value I paste it there. However, I am having difficulty to write macro that will paste it in the last row when I cannot find a proper month.

Below you can find my code, feel more than free to make any changes.

Dim wsCopy2 As Worksheet
Dim wsDest2 As Worksheet

Set wsCopy2 = Workbooks("workbook2").Worksheets("Sheet1")
Set wsDest2 = Workbooks("workbook1").Worksheets("Sheet1")

Dim CopyLastRow2 As Long
CopyLastRow2 = wsCopy2.Cells(wsCopy2.Rows.Count, "A").End(xlUp).Row

Dim StartRow As Long
StartRow = wsDest2.Range("C:C").Find(what:=wsCopy2.Range("B1"), after:=wsDest2.Range("C1")).Row

wsCopy2.Range("A1:Y" & CopyLastRow2).Copy _
    wsDest2.Range("B" & StartRow)

What I am having problem is: If [...] End If that will paste "prepared data" to the last row in a new month (i.e. in a situation when it cannot find "B1" value).

Dim DestLastRow2 As Long
DestLastRow2 = wsDest2.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1).Row

If [...] Then
wsCopy2.Range("A1:Y" & CopyLastRow2).Copy _
    wsDest2.Range("B" & DestLastRow2)
Else
wsCopy2.Range("A1:Y" & CopyLastRow2).Copy _
    wsDest2.Range("B" & StartRow)
End If

As a VBA newbie I am trying multiple approaches and get as many different errors as possible - but the most common was "object variable or With block variable not set (error 91)" about the line:

StartRow = wsDest2.Range("C:C").Find(what:=wsCopy2.Range("B1"), after:=wsDest2.Range("C1")).Row

SJR SJR · Accepted Answer · 2019-08-12T12:33:57

The 91 error arises because you are trying to assign to StartRow the row of a cell which does not exist because the search term wasn't found.

Instead, assign the result of Find to a range variable and then you can check whether it returns anything before trying to return the row property.

It's worth specifying more Find parameters too, just in case they are not what you think they are.

Sub x()

Dim StartRow As Range
Dim DestLastRow2 As Long

DestLastRow2 = wsDest2.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1).Row
Set StartRow = wsDest2.Range("C:C").Find(what:=wsCopy2.Range("B1"), after:=wsDest2.Range("C1"))

If StartRow Is Nothing Then                     'not found
    wsCopy2.Range("A1:Y" & CopyLastRow2).Copy _
        wsDest2.Range("B" & DestLastRow2)
Else                                            'found
    wsCopy2.Range("A1:Y" & CopyLastRow2).Copy _
        wsDest2.Range("B" & StartRow.Row)
End If

End Sub

VBA copy-paste in a weekly generated report

1 Answers