Why is my VBA code giving me an error.

[helpneeded2]

I was using the code below fine for 2 days, and now it is giving me an error. Any suggestions as to why?

The error I am getting is "Run-time error '1004': PasteSpecial method of Range class failed

Sub Obtain_OLG_Patron_Data()
    Dim wbkSource As Workbook
    Dim endOfRows As Long
 
    Set wbkSource = ThisWorkbook
   
    ' Open, find last row of data, and copy data from OLG MASTER CSV File
    Workbooks.Open ("G:\Department\Division\Unit\Protected Document\OLG Master Lists\OLG MASTER TO DATE.xlsx")
    endOfRows = ActiveSheet.Range("A:A").Find("*", , xlValues, , xlByRows, xlPrevious).Row
    Range("A1").Resize(endOfRows, 42).Select
    Selection.Copy
   
    ' Paste Data into OLG Sheet
    wbkSource.Worksheets("OLG_MASTER").Range("A2").PasteSpecial Paste:=xlPasteValues
   
    ' Copy cell value to quickly clear clipboard
    wbkSource.Worksheets("OLG_MASTER").Range("A2").Copy
   
    ' Close OLG MASTER CSV File
    Workbooks("OLG MASTER TO DATE.xlsx").Close SaveChanges:=False
   
    ' Return focus to OLG Sheet, and cancel copy mode
    wbkSource.Worksheets("OLG_MASTER").Range("A2").Select
    Application.CutCopyMode = False

End Sub

 

[MARK858]

Do you get an error if you change

   Range("A1").Resize(endOfRows, 42).Select
    Selection.Copy
   
    ' Paste Data into OLG Sheet
    wbkSource.Worksheets("OLG_MASTER").Range("A2").PasteSpecial Paste:=xlPasteValues
    
    ' Copy cell value to quickly clear clipboard
    wbkSource.Worksheets("OLG_MASTER").Range("A2").Copy

to

    With Range("A1").Resize(endOfRows, 42)

        wbkSource.Worksheets("OLG_MASTER").Range("A2").Resize(.Rows.Count, .Columns.Count).Value = .Value
    
    End With

 

[helpneeded2]

Thanks Mark, that does make it work.

I've very new to VBA and have no familiarity with the use of "with" statements, so now I have something new to research and learn. Thank you.

 

[MARK858]

You're welcome

A With statement is just a way to shorten/tidy code when you have the same object repeated (it does also mean Excel only has to evaluate it once).
If I didn't use the With statement then the code would have been

wbkSource.Worksheets("OLG_MASTER").Range("A2").Resize(Range("A1").Resize(endOfRows, 42).Rows.Count, Range("A1").Resize(endOfRows, 42).Columns.Count).Value = Range("A1").Resize(endOfRows, 42) .Value