May 24 2018
04:52 AM
- last edited on
Jul 25 2018
10:01 AM
by
TechCommunityAP
May 24 2018
04:52 AM
- last edited on
Jul 25 2018
10:01 AM
by
TechCommunityAP
Hi,
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.
The issue i'm having is that the VBA won't run and presents as 'error 424 Object required'.
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.
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"
Attached workbook - can anyone help solve why this won't work?
Kindest Regards,
Georgina Sothcott-Gilson
May 24 2018 09:10 PM
I think the VBA command is actually Workbooks.Open not Workbook.open
Try using this syntax instead. You will also need to specify a file extension. So maybe this:
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
May 25 2018 01:21 AM
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.
May 25 2018 01:24 AM
This line is the issue:
Worksheets("DATA Referal numbers").Select
Full:
Sub AccumlatedDataforQuaterlyReports()
Dim LastRow As Integer, i As Integer, erow As Integer
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
Range(Cells(i, 1), Cells(i, 13)).Select
Selection.Copy
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"
Worksheets("DATA Referal numbers").Select
erow = AactiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Next i
End Sub
May 25 2018 06:23 AM - edited May 29 2018 06:40 PM
May 25 2018 06:23 AM - edited May 29 2018 06:40 PM
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
May 31 2018 06:30 AM
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.