Need help with VBA - error 424 object required

Hey Georgina-

I just wanted to follow up and see if you were able to get this code working the way you expected?  Please feel free to post back to the community if you need additional help or clarification.

Georgina-

Happy to help you accomplish your end goal

The Subscript Out of Range error typically means the worksheet you're referencing does not exist in the workbook.  Do you have any trailing or leading spaces in the name?  make sure it is spelled exactly the same.

If you put the Workbooks.Open code line in a loop in will error on the second iteration because it will already be open.

Here is the amended code with notes on some good practices.  Hope this helps.

Sub AccumlatedDataforQuaterlyReports()

    'Declarations
    'Try to make the variables meaningful
    'So others can understand the purpose of the variables
    Dim SrcLrow As Integer
    Dim intLp As Integer
    Dim DestLrow As Integer
    Dim DestWkBk As Workbook
    Dim SrcWkBk As Workbook
    Dim SrcSht As Worksheet
    Dim DestSht As Worksheet
    
    Set SrcWkBk = ThisWorkbook 'Define Source workbook
    Set SrcSht = SrcWkBk.ActiveSheet 'Define Active Worksheet
    SrcLrow = SrcSht.Range("A" & Rows.Count).End(xlUp).Row 'Define last row of current worksheet
    
    'This goes outside of the loop.  You don't want to keep trying to open the
    'workbook... it will cause code errors
    'Open Master Workbook (Destination Workbook) and set object variable
    Set DestWkBk = Workbooks.Open(Filename:="\\thebridgeeastmidlands-my.sharepoint.com@SSL\personal\data_thebridge-eastmidlands_org_uk\Business Services\CST\16. Communications\14. Reporting\Quaterly Reports\MASTER REPORT.xlsx")
                                    
    Set DestSht = DestWkBk.Sheets("DATA Referal number") 'Define Destination Worksheet
                                    
    For intLp = 2 To SrcLrow
    
        'Define Last Row in Destination Workbook
        DestLrow = DestSht.Range("A" & Rows.Count).End(xlUp).Row
    
        'Copy and Paste Data
        SrcSht.Range("A" & intLp & ":M" & intLp).Copy DestSht.Range("A" & DestLrow + 1)

    Next intLp
    
    'These statements go outside of the loop.
    'You don't want to keep saving and closing
    'the workbook
    
    DestWkBk.Save
    DestWkBk.Close

End Sub