Saving sheets to pdf IF they are there

Muffins29

New Member
Joined
Jun 15, 2012
Messages
8
I'd like to have this macro repeat itself for sheets if they're in the workbook. The only thing to change would be the sheet number. Next grouping would be sheet 7, 6....then 9, 8...and so on. I'd like to to be able to be "infinite" with the number of sheets it could do. Is there a way to write so it adds 2 sheets after running the previous section? And I'd like to have it do the next section, if the previous section has run. Any suggestions?

MyFileName = Sheets(5).Range("D3").Value
MyFileNameTwo = Sheets(5).Range("E2").Value
MyFileNameThree = Sheets(5).Range("E1").Value
ChDir MyFileName
Sheets(4).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=(MyFileNameTwo & ".pdf"), Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Sheets(5).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=(MyFileNameThree & ".pdf"), Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Sheets(1).Select
Range("A1").Select
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Maybe something like this if I understand your question...

Code:
    [COLOR=darkblue]For[/COLOR] n = 4 [COLOR=darkblue]To[/COLOR] Sheets.Count [COLOR=darkblue]Step[/COLOR] 2
    
        MyFileName = Sheets(n + 1).Range("D3").Value
        MyFileNameTwo = Sheets(n + 1).Range("E2").Value
        MyFileNameThree = Sheets(n + 1).Range("E1").Value
        
        ChDir MyFileName
        
        [COLOR=green]'Sheets 4,6,8 etc...[/COLOR]
        Sheets(n).ExportAsFixedFormat Type:=xlTypePDF, Filename:=(MyFileNameTwo & ".pdf"), Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=[COLOR=darkblue]False[/COLOR]
        
        [COLOR=green]' Sheets 5,7,9 etc...[/COLOR]
        Sheets(n + 1).ExportAsFixedFormat Type:=xlTypePDF, Filename:=(MyFileNameThree & ".pdf"), Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=[COLOR=darkblue]False[/COLOR]
        
        [COLOR=green]'Application.Goto Sheets(1).Range("A1")[/COLOR]
        
    [COLOR=darkblue]Next[/COLOR] n
 
Upvote 0
Yes that's what I wanted to do. I'm self teaching myself how to "write" macros and run into trouble getting it to do what I want. These forums are excellent and thank you very much!
 
Upvote 0

Forum statistics

Threads
1,211,772
Messages
6,103,881
Members
447,883
Latest member
bennazi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top