I am trying to code a routine which takes a user's choices of available worksheets from an Excel VBA form and exports them into one PDF document. I plan on using this with export to Word and to PowerPoint routines. I have tried several of the ideas from Stack Overflow which were said to have worked. I have had no luck.
![](/uploads/1/2/6/6/126641439/815411683.jpg)
![Vba Vba](/uploads/1/2/6/6/126641439/352927987.jpg)
Excel for Office 365 Excel for Office 365 for Mac Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel for Mac 2011 More. Less If you need to develop complex statistical or engineering analyses, you can save steps and time by using the Analysis ToolPak. Exporting specified area as a PDF with VBA on Mac 2016 (self.excel) submitted 7 months ago by RGDale. Currently trying to write a script that filters data and creates new tabs named the value that the data is being filtered.
I have also tried various ideas from other sources.maybe I am blind to the obvious.I tried using an array (arrSheets, still in the code, I have hopes that maybe I can still use it.). It populates with sheet objects, but using a dynamic array and the redim command haven't worked for me.What I have here is code which appears to work well until it comes to the 'ActiveSheet.ExportAsFixedFormat.' At that point I get 'Application-defined or Object-defined error (Run-time error 1004)'The following code fires from a command button on a VBA form. The only reason of failure that I can think of for your ExportAsFixedFormat statement is that the option DisplayFileAfterPublish is not enabled in your installation (typical if you don't have Acrobat Reader installed). You can check it when you try to export manually to PDF, the option 'open file after publishing' must be disabled.Try removing the parameter DisplayFileAfterPublish: ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:='C:test.pdf', Quality:=xlQualityStandard, IgnorePrintAreas:=FalseAs for the other questions in your post, I dont see anything wrong in putting Worksheet objects in an array. But Sheets(arrSheets).Select is ill-formed and morover unnecessary. It is ill-formed because the arrSheets argument is an array containing sheet references, while it should be an array of indices, that's all.Also I can't tell why Redim hasn't worked for you, because you did not show how you used it.
This works for me. It has the defect (which also existed in OP's original) that the sheets selected may not be the ones you intended since you're selecting on sheet number according to the tabindex of the checkboxes on the form.
![Vba Vba](/uploads/1/2/6/6/126641439/267845018.png)
How you correct for this is up to you, it's out of scope here. Private Sub CommandButton1ClickDim intSelectionNum As IntegerDim bolFound As BooleanDim ctrl As ControlbolFound = True ' was a checked box found? True = NOintSelectionNum = 0 ' which checkbox is itFor Each ctrl In FrmToPDF.ControlsIf TypeName(ctrl) = 'CheckBox' ThenintSelectionNum = intSelectionNum + 1 ' set the selection numberIf ctrl.Value = True ThenThisWorkbook.Sheets(intSelectionNum).Select bolFoundbolFound = False ' from now on we extend the selectionEnd IfEnd IfNextIf bolFound = True Then ' if there is Nothing selected send a message, or do the deal.MsgBox 'There is nothing selected to export!' , vbOKOnly, 'Nothing selected.' ElseActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:='C:test.pdf', Quality:=xlQualityStandard, IgnorePrintAreas:=FalseEnd IfEnd SubWhat's happening here: I am overloading bolFound to set an optional Worksheets.Select parameter - True means replace the current slection, False means extend the current selection.
By starting bolFound as True, I nullilfy any existing selection. BolFound is then set to False, which extends the selection for subsequent sheets. I don't need an array as I am making Excel manage the selection for me.Note: ThisWorkbook.Sheets(intSelectionNum).Select only selects one sheet at a time (default is True), so OP's code as posted would only export one sheet at a time no matter how many boxes were checked.I have Acrobat Readeer installed and, DisplayFileAfterPublish:=True worked for me, but I have omitted that part from the example.Bonus discussion: Like OP's code the example selects 'Sheets' instead of 'Worksheets'.
This allows it to export e.g. Charts, Excel4 macros and dialogs, and so on (though by now only the Charts will of interest to most people). If I changed 'Sheets' to 'Worksheets' it would export only worksheets.Mandatory disclaimer: this is example code to export selected sheets to a PDF. It has not been extensively tested and is not intended to be a drop-in solution. It works for me. Depending on your system you may need to tweak it.Edited to add: to find out what sheets were selected (e.g. To build your array to use for other purposes), you can insert this after the For/Next loop finishes: dim sht as ObjectFor Each sht In Application.ActiveWindow.SelectedSheetsDebug.Print sht.NameNext.
![](/uploads/1/2/6/6/126641439/815411683.jpg)