Run-Time error 424 - Export to PDF Sheets - Array

AFidytek

New Member
Joined
Jun 5, 2019
Messages
23
Hi all

I am very new to VB and its mostly thanks to forums like this and a bit of internet searching.

I have a Run-time error 424 - Object required issue

Let me explain what I'm doing:
I have a commandButton that actions SaveAs PDF
The one button has to only export 2 specific sheets to PDF
The msgbox pops up and is giving me the correct filename (which I have selected)
But when I say save after that it ends with the runtime error - I am presuming its the sheets (array argument

Please can someone look at this for me and tell me what I am doing wrong:

Private Sub CommandButton2_Click()
answer = msgbox("Do you want to export the quotation to PDF?", vbYesNo + vbQuestion, "Export Quotation to PDF")
If answer = vbYes Then
filesSaveName = Application.GetSaveAsFilename(Sheet49.Range("N13"), fileFilter:="PDF(*.pdf),*.pdf")
If fileSaveName <> True Then
Sheets(Array("AutoRate Quote Form", "Excess Sheet")).Select
ActiveSheets.ExportAsFixedFormat Type:=xlTypePDF, filename:=fileSaveName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End If
End If
End Sub

Thanks in advance
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,564
Office Version
365
Platform
Windows
There's no array in that code.

The only thing I can see that might cause the error you describe is if you don't have a worksheet with the codename Sheet49 in the active worksheet.
 

AFidytek

New Member
Joined
Jun 5, 2019
Messages
23
Sheet 49 reference is working fine. That's the filename sheet (the active one).

The error is this part i think:

Sheets(Array("AutoRate Quote Form", "Excess Sheet")).Select

Since this is the only difference (besides activesheets & activeworkbook)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,564
Office Version
365
Platform
Windows
Is that the line causing the error?

If it is do you have sheets named 'AutoRate Quote Form' and 'Excess Sheet' in the active workbook?

If it isn't which line is causing the error and what's the error message?
 

AFidytek

New Member
Joined
Jun 5, 2019
Messages
23
Yes I have those sheets Sheet49 and Sheet26

It's not highlighting the error line.
 

AFidytek

New Member
Joined
Jun 5, 2019
Messages
23
I got it to work:

Code:
Private Sub CommandButton2_Click()answer = msgbox("Do you want to export the quotation to PDF?", vbYesNo + vbQuestion, "Export Quotation to PDF")If answer = vbYes ThenfileSaveName = Application.GetSaveAsFilename(Sheet49.Range("N13"), fileFilter:="PDF(*.pdf),*.pdf")If fileSaveName <> False ThenSheets(Array("AutoRate Quote Form", "Excess Sheet")).SelectActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=fileSaveName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True    End IfEnd IfEnd Sub
Thanks for the help Norie

A</div>
 

Watch MrExcel Video

Forum statistics

Threads
1,100,061
Messages
5,472,223
Members
406,809
Latest member
haf19

This Week's Hot Topics

Top