#1
|
|||
|
|||
Macro to save to a single PDF
Hi! I am new to VBE in Excel, so please bare with me FYI, I am a Mac Excel 2011 user. I have set up a marco button that when clicked, a pop up displays listing all the active worksheets. The user selects the sheets he/she wants to print, then it prints to the default printer. But I now want to set this up so it saves all selected worksheets to a SINGLE pdf file. Ideally, I would also like it to ask the user what file name he/she wants to save it as. And select a destination. Below is my current code. Any advice would be greatly appreciated. Thank you! Code:
Private Sub CheckBox2_Click() End Sub Private Sub CommandButton1_Click() If CheckBox1.Value = True Then Sheets("Jan").PrintOut Copies:=1 If CheckBox2.Value = True Then Sheets("Feb").PrintOut Copies:=1 If CheckBox3.Value = True Then Sheets("Mar").PrintOut Copies:=1 If CheckBox4.Value = True Then Sheets("Apr").PrintOut Copies:=1 If CheckBox5.Value = True Then Sheets("May").PrintOut Copies:=1 If CheckBox6.Value = True Then Sheets("Jun").PrintOut Copies:=1 If CheckBox7.Value = True Then Sheets("Jul").PrintOut Copies:=1 If CheckBox8.Value = True Then Sheets("Aug").PrintOut Copies:=1 If CheckBox9.Value = True Then Sheets("Sep").PrintOut Copies:=1 If CheckBox10.Value = True Then Sheets("Oct").PrintOut Copies:=1 If CheckBox11.Value = True Then Sheets("Nov").PrintOut Copies:=1 If CheckBox12.Value = True Then Sheets("Dec").PrintOut Copies:=1 If CheckBox13.Value = True Then Sheets("Jan (2)").PrintOut Copies:=1 If CheckBox14.Value = True Then Sheets("Feb (2)").PrintOut Copies:=1 If CheckBox15.Value = True Then Sheets("Mar (2)").PrintOut Copies:=1 If CheckBox16.Value = True Then Sheets("Apr (2)").PrintOut Copies:=1 If CheckBox17.Value = True Then Sheets("May (2)").PrintOut Copies:=1 If CheckBox18.Value = True Then Sheets("Jun (2)").PrintOut Copies:=1 If CheckBox19.Value = True Then Sheets("Jul (2)").PrintOut Copies:=1 If CheckBox20.Value = True Then Sheets("Aug (2)").PrintOut Copies:=1 If CheckBox21.Value = True Then Sheets("Sep (2)").PrintOut Copies:=1 If CheckBox22.Value = True Then Sheets("Oct (2)").PrintOut Copies:=1 If CheckBox23.Value = True Then Sheets("Nov (2)").PrintOut Copies:=1 If CheckBox24.Value = True Then Sheets("Dec (2)").PrintOut Copies:=1 Unload UserForm1 End Sub Private Sub UserForm_Initialize() CheckBox1.Caption = Sheets("Jan").Name CheckBox2.Caption = Sheets("Feb").Name CheckBox3.Caption = Sheets("Mar").Name CheckBox4.Caption = Sheets("Apr").Name CheckBox5.Caption = Sheets("May").Name CheckBox6.Caption = Sheets("Jun").Name CheckBox7.Caption = Sheets("Jul").Name CheckBox8.Caption = Sheets("Aug").Name CheckBox9.Caption = Sheets("Sep").Name CheckBox10.Caption = Sheets("Oct").Name CheckBox11.Caption = Sheets("Nov").Name CheckBox12.Caption = Sheets("Dec").Name CheckBox13.Caption = Sheets("Jan (2)").Name CheckBox14.Caption = Sheets("Feb (2)").Name CheckBox15.Caption = Sheets("Mar (2)").Name CheckBox16.Caption = Sheets("Apr (2)").Name CheckBox17.Caption = Sheets("May (2)").Name CheckBox18.Caption = Sheets("Jun (2)").Name CheckBox19.Caption = Sheets("Jul (2)").Name CheckBox20.Caption = Sheets("Aug (2)").Name CheckBox21.Caption = Sheets("Sep (2)").Name CheckBox22.Caption = Sheets("Oct (2)").Name CheckBox23.Caption = Sheets("Nov (2)").Name CheckBox24.Caption = Sheets("Dec (2)").Name End Sub Last edited by LukeExcelKid; 11-16-2013 at 07:59 PM. |
#2
|
|||
|
|||
Does anyone have any advice? Thanks in advance.
|
#3
|
||||
|
||||
Hi LukeExcelKid,
Please be patient - some of us have lives to live outside of answering questions on the net. If your Mac has a PDF 'printer', try something based on: Code:
Private Sub CommandButton1_Click() Dim wkSht As Worksheet, Ctrl As Control, i As Long, ArrShts() With ActiveWorkbook ReDim Preserve ArrShts(0) Set wkSht = .ActiveSheet: i = -1 For Each Ctrl In Me.Controls With Ctrl If .Name Like "CheckBox#*" Then If .Value = True Then i = i + 1 ReDim Preserve ArrShts(i) ArrShts(i) = .Caption End If End If End With Next If UBound(ArrShts()) = 0 Then GoTo NoPrint .Sheets(ArrShts).Select Application.Dialogs(xlDialogPrint).Show Arg1:=1, Arg4:=1, Arg5:=False, Arg6:=True, Arg7:=1 wkSht.Activate End With NoPrint: Unload UserForm1 End Sub Private Sub UserForm_Initialize() CheckBox1.Caption = "Jan" CheckBox2.Caption = "Feb" CheckBox3.Caption = "Mar" CheckBox4.Caption = "Apr" CheckBox5.Caption = "May" CheckBox6.Caption = "Jun" CheckBox7.Caption = "Jul" CheckBox8.Caption = "Aug" CheckBox9.Caption = "Sep" CheckBox10.Caption = "Oct" CheckBox11.Caption = "Nov" CheckBox12.Caption = "Dec" CheckBox13.Caption = "Jan (2)" CheckBox14.Caption = "Feb (2)" CheckBox15.Caption = "Mar (2)" CheckBox16.Caption = "Apr (2)" CheckBox17.Caption = "May (2)" CheckBox18.Caption = "Jun (2)" CheckBox19.Caption = "Jul (2)" CheckBox20.Caption = "Aug (2)" CheckBox21.Caption = "Sep (2)" CheckBox22.Caption = "Oct (2)" CheckBox23.Caption = "Nov (2)" CheckBox24.Caption = "Dec (2)" End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
|||
|
|||
Hi macropod,
Thank you for your quick reply. This works well! The one thing I noticed is that it doesn't work if you only select one checkbox (one worksheet) to save/print to PDF. It works for (2) or more worksheets. Is there an adjustment to the code to allow a user to save/print one sheet if selected? |
#5
|
||||
|
||||
Try:
Code:
Private Sub CommandButton1_Click() Dim wkSht As Worksheet, Ctrl As Control, i As Long, ArrShts() ReDim Preserve ArrShts(0) For Each Ctrl In Me.Controls With Ctrl If .Name Like "CheckBox#*" Then If .Value = True Then ReDim Preserve ArrShts(i) ArrShts(i) = .Caption i = i + 1 End If End If End With Next If ArrShts(0) = 0 Then GoTo NoPrint With ActiveWorkbook Set wkSht = .ActiveSheet .Sheets(ArrShts).Select Application.Dialogs(xlDialogPrint).Show Arg1:=1, Arg4:=1, Arg5:=False, Arg6:=True, Arg7:=1 wkSht.Activate End With NoPrint: Unload UserForm1 End Sub Private Sub UserForm_Initialize() Dim Ctrl As Control, i As Long, ArrShts() ArrShts() = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", _ "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", _ "Jan (2)", "Feb (2)", "Mar (2)", "Apr (2)", "May (2)", "Jun (2)", _ "Jul (2)", "Aug (2)", "Sep (2)", "Oct (2)", "Nov (2)", "Dec (2)") For Each Ctrl In Me.Controls With Ctrl If .Name Like "CheckBox#*" Then .Caption = ArrShts(i) i = i + 1 End If End With Next End Sub If ArrShts(0) = 0 Then GoTo NoPrint
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Macro to save as pdf with ability to choose save as folder | rvessio | Word VBA | 4 | 07-25-2016 12:37 PM |
Change single character in PPT to another font macro | rtwwpad | PowerPoint | 1 | 01-19-2013 05:08 PM |
Macro to save as PDF but with a different name | shabbaranks | Word VBA | 2 | 05-20-2011 01:02 AM |
Macro to Save Help | clarkson001 | Word | 0 | 02-14-2011 06:41 AM |
Macro Won't Save | lou0915 | Word VBA | 2 | 10-17-2009 08:13 PM |