Need help with VBA - error 424 object required

Question

Hi,&nbsp;&nbsp;I am creating a big report - that amalgamates about 9 different reports from our system.T do this I have created a VBA to run all of these reports into the one spreadsheet, where I can then create a Macro that puts all of this into the end product/report.&nbsp;&nbsp;The issue i'm having is that the VBA won't run and presents as 'error 424 Object required'.&nbsp;When I press 'Debug' it takes me to the coding and highlights in yellow the workbook line - where I am specifying what file I need the VBA to open and paste the data into.&nbsp;&nbsp;Workbook.Open Filename:="\thebridgeeastmidlands-my.sharepoint.com@SSL\personal\data_thebridge-eastmidlands_org_uk\Business Services\CST\16. Communications\14. Reporting\Quaterly Reports\MASTER REPORT"&nbsp;Attached workbook - can anyone help solve why this won't work?&nbsp;Kindest Regards,&nbsp;Georgina Sothcott-Gilson

matt mickle · Answer

I think the VBA command is actually&nbsp;Workbooks.Open not Workbook.open
&nbsp;
&nbsp;
Try using this syntax instead. You will also need to specify a file extension.&nbsp; So maybe this:
&nbsp;
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

georgina sothcott-gilson · Answer

Thank you so much for replying, it worked!

But.... now it is saying 'Run-time error '9': Subscript out of range'

Are you able to help with this one too?

Thank you so much in advance.

georgina sothcott-gilson · Answer

This line is the issue:Worksheets("DATA Referal numbers").Select&nbsp;Full:Sub AccumlatedDataforQuaterlyReports()Dim LastRow As Integer, i As Integer, erow As IntegerLastRow = ActiveSheet.Range("A" &amp; Rows.Count).End(xlUp).RowFor i = 2 To LastRowRange(Cells(i, 1), Cells(i, 13)).SelectSelection.CopyWorkbooks.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"Worksheets("DATA Referal numbers").Selecterow = AactiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).RowActiveSheet.Cells(erow, 1).SelectActiveSheet.PasteActiveWorkbook.SaveActiveWorkbook.CloseNext iEnd Sub

matt mickle · Answer

Georgina-
&nbsp;
Happy to help you accomplish your end goal
&nbsp;
The Subscript Out of Range error typically means the worksheet you're referencing does not exist in the workbook.&nbsp; Do you have any trailing or leading spaces in the name?&nbsp; make sure it is spelled exactly the same.
&nbsp;
If you put the Workbooks.Open code line in a loop in will error on the second iteration because it will already be open.
&nbsp;
Here is the amended code with notes on some good practices.&nbsp; Hope this helps.
&nbsp;
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" &amp; 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" &amp; Rows.Count).End(xlUp).Row
    
        'Copy and Paste Data
        SrcSht.Range("A" &amp; intLp &amp; ":M" &amp; intLp).Copy DestSht.Range("A" &amp; 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

&nbsp;
&nbsp;

matt mickle · Answer

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

Forum Discussion

Need help with VBA - error 424 object required

5 Replies

Resources