Hello,
I'm new to this forum and what better way to test the effectiveness / use of this place than to ask VBE questions! Essentially, I'm trying to write a code which will take multiple sheets with a workbook and save them individually as a .pdf displaying only that sheet.
Some background: I am the VPFA of my school's student government and I need to be able to produce a weekly .pdf version of every student organization's budgets. I have a single master budget sheet with all orgs on it and want a Macro to run either a Export or Save as function which would produce a .pdf document for each org and saved within a specified document and under a specified name. I used the record macro button but it saves the whole workbook instead of the single sheet. can you help?
Here is the code I have now:
Sub Updater()
'
' Updater Macro
' Test
'
' Keyboard Shortcut: Option+Cmd+n
'
Sheets("Org. 6th").Select
ChDir _
"Macintosh HD:Users:ndemasie:Dropbox:Organization Dropboxs.pdf:The 6th Man Club:"
Range("A1:B1").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:The 6th Man Club:6th.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. ACM").Select
Range("I25").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Association of Computing Machinery:ACM.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. ASID").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:American Society of Interior Design:ASID.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. Budokai").Select
Range("M12").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:IWU Budokai:Budokai.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. CCA").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Christian Care Alliance:CCA.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. CEC").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Council for Exceptional Children:CEC.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. CJC").Select
Range("I12").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Criminal Justice Club:CJC.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. CR").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:College Republicans:CR.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. Doulos").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Doulos:Doulos.Budget.pdf", FileFormat _
:=xlPDF, PublishOption:=xlSheet
Sheets("Org. E&W").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:English & Writing Club:E&W.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. IWU HC").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:IWU History Club:IWU HC.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. HCSA").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Honors College Student Association:HCSA.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. ICDO").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:International and Community Development:ICDO.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. IWU CA").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:IWU Community of Accountants:IWU CA.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. IWU Lead").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:IWU Leadership Society:IWU Lead.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. IWU RC").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:IWU Running Club:IWURC.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. KDP").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Kappa Delta Pi:KDP.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. LAH").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:La Amistad Hispanica:LAH.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. NAME").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:National Association of Music Education:NAME.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. NCF").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Nurses Christian Fellowship:NCF.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. PAT").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Phi Alpha Theta:PAT.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. PRSSA").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Public Relations Student Society:PRSSA.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. Psi Chi").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Psi Chi:Psi Chi.Budget.pdf", FileFormat _
:=xlPDF, PublishOption:=xlSheet
Sheets("Org. Psych").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Psychology Club:Psych.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. Remix").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:IWU Remix:Remix.Budget.pdf", FileFormat _
:=xlPDF, PublishOption:=xlSheet
Sheets("Org. SC").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Science Club:SC.Budget.pdf", FileFormat _
:=xlPDF, PublishOption:=xlSheet
Sheets("Org. SEA").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Student Education Association:SEA.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. SIFE").Select
Range("B23:C24").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Student in Free Enterprise:SIFE.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. SWC").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Social Work Club:SWC.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. UN").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Model UN:UN.Budget.pdf", FileFormat:= _
xlPDF, PublishOption:=xlSheet
Sheets("Org. WCF").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:World Christian Fellowship:WCF.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
End Sub
I'm new to this forum and what better way to test the effectiveness / use of this place than to ask VBE questions! Essentially, I'm trying to write a code which will take multiple sheets with a workbook and save them individually as a .pdf displaying only that sheet.
Some background: I am the VPFA of my school's student government and I need to be able to produce a weekly .pdf version of every student organization's budgets. I have a single master budget sheet with all orgs on it and want a Macro to run either a Export or Save as function which would produce a .pdf document for each org and saved within a specified document and under a specified name. I used the record macro button but it saves the whole workbook instead of the single sheet. can you help?
Here is the code I have now:
Sub Updater()
'
' Updater Macro
' Test
'
' Keyboard Shortcut: Option+Cmd+n
'
Sheets("Org. 6th").Select
ChDir _
"Macintosh HD:Users:ndemasie:Dropbox:Organization Dropboxs.pdf:The 6th Man Club:"
Range("A1:B1").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:The 6th Man Club:6th.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. ACM").Select
Range("I25").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Association of Computing Machinery:ACM.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. ASID").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:American Society of Interior Design:ASID.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. Budokai").Select
Range("M12").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:IWU Budokai:Budokai.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. CCA").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Christian Care Alliance:CCA.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. CEC").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Council for Exceptional Children:CEC.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. CJC").Select
Range("I12").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Criminal Justice Club:CJC.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. CR").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:College Republicans:CR.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. Doulos").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Doulos:Doulos.Budget.pdf", FileFormat _
:=xlPDF, PublishOption:=xlSheet
Sheets("Org. E&W").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:English & Writing Club:E&W.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. IWU HC").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:IWU History Club:IWU HC.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. HCSA").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Honors College Student Association:HCSA.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. ICDO").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:International and Community Development:ICDO.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. IWU CA").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:IWU Community of Accountants:IWU CA.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. IWU Lead").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:IWU Leadership Society:IWU Lead.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. IWU RC").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:IWU Running Club:IWURC.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. KDP").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Kappa Delta Pi:KDP.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. LAH").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:La Amistad Hispanica:LAH.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. NAME").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:National Association of Music Education:NAME.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. NCF").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Nurses Christian Fellowship:NCF.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. PAT").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Phi Alpha Theta:PAT.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. PRSSA").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Public Relations Student Society:PRSSA.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. Psi Chi").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Psi Chi:Psi Chi.Budget.pdf", FileFormat _
:=xlPDF, PublishOption:=xlSheet
Sheets("Org. Psych").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Psychology Club:Psych.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. Remix").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:IWU Remix:Remix.Budget.pdf", FileFormat _
:=xlPDF, PublishOption:=xlSheet
Sheets("Org. SC").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Science Club:SC.Budget.pdf", FileFormat _
:=xlPDF, PublishOption:=xlSheet
Sheets("Org. SEA").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Student Education Association:SEA.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. SIFE").Select
Range("B23:C24").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Student in Free Enterprise:SIFE.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. SWC").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Social Work Club:SWC.Budget.pdf", _
FileFormat:=xlPDF, PublishOption:=xlSheet
Sheets("Org. UN").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:Model UN:UN.Budget.pdf", FileFormat:= _
xlPDF, PublishOption:=xlSheet
Sheets("Org. WCF").Select
Range("B22:C23").Select
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:ndemasie:Desktop:O:World Christian Fellowship:WCF.Budget.pdf" _
, FileFormat:=xlPDF, PublishOption:=xlSheet
End Sub