Subscript out of range Run time error'9' in macro used to copy from one sheet to another

Question

I am completely new to writing macros and just getting to know how it works. I need to copy data from one workbook onto another one, this is the macro that i came up with:

Option Explicit
Sub CopyMainTable()
    Dim SourceWorkbook As Workbook
    Dim DestWorkbook As Workbook
    
    ' Set references to the source and destination workbooks
    Set SourceWorkbook = Workbooks("Monthly Report.xlsx")
    Set DestWorkbook = Workbooks("ANNUAL REPORT.xlsx")
    
    ' Copy data from source to destination
    SourceWorkbook.Sheets("SITE1").Range("B19:Av43").Copy Destination:=DestWorkbook.Sheets("SITE1").Range("B19")
    
    Set SourceWorkbook = Nothing
    Set DestWorkbook = Nothing

    
End Sub

Problem is that when it runs it comes up with a run-time error 9: subscript out of range, i checked the cells on both workbooks and they do match one to one

I made sure al names were correctly spelled, and checked the cells on the worksheets

Answer 1

Option Explicit

Sub CopyMainTable()

    Dim wbSource As Workbook, wbDest As Workbook
    Dim wsSource As Worksheet
    Dim rngCopy As Range
    Dim sFolder As String, sourceFile As String, s As String
   
    ' Set references to the source and destination workbooks
    Set wbDest = ThisWorkbook
    sFolder = wbDest.Path & "\"
    sourceFile = "Monthly Report.xlsx"
    
    ' check source file exists then open
    s = sFolder & sourceFile
    On Error Resume Next
    Set wbSource = Workbooks.Open(s, ReadOnly:=True)
    If wbSource Is Nothing Then
        MsgBox sourceFile & " not found in " & sFolder, vbCritical
        Exit Sub
    Else
        ' check sheet
        Set wsSource = wbSource.Sheets("SITE1")
        If wsSource Is Nothing Then
            MsgBox "Sheet SITE1 not found in " & wbSource.Name, vbCritical
            wbSource.Close
            Exit Sub
        End If
    End If
    On Error GoTo 0
    
    ' Copy data from source to destination
    Set rngCopy = wsSource.Range("B19:AA43")
    rngCopy.Copy Destination:=wbDest.Sheets("SITE1").Range("B19")
    
    MsgBox rngCopy.Address & " copied from " & wbSource.Name, vbInformation
    wbSource.Close

End Sub