Need some help with VBA loop/referencing, please.

[judgepax]

BIG PICTURE: Create a script that at the touch of a button will consistently format and save each worksheet as its own .pdf.

WHAT I HAVE SO FAR: I have a working VBA for the formatting and a working VBA for the "Save As... pdf", but I am stuck on how to properly do a VBA reference loop. I have the following script refencing a simple "SheetList" (name of every tab in the workbook). I want the script to repeat for Sheets 1 through x but I can't figure out how to have the same two lines of code repeat until VBA finishes last x sheet. Possibly something with a SheetCount?

Sub SaveAspdf()
 
    Sheets(1).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Desktop\" & Worksheets("SheetList").Range.Cells(1,1), Quality:=xlQualityStandard, OpenAfterPublish:=False
 
    Sheets(2).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Desktop\" & Worksheets("SheetList").Range.Cells(1,2), Quality:=xlQualityStandard, OpenAfterPublish:=False
 
    Sheets(3).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Desktop\" & Worksheets("SheetList").Range.Cells(1,3), Quality:=xlQualityStandard, OpenAfterPublish:=False
 
End Sub

VBA is not my strong point, so I appreciate any help I can get.

 

[Cindy Ellis]

Something like this? (Change the directory t"C:\desktop'" to your chosen path):

Sub TestMakePdf()
Dim WS As Worksheet
    For Each WS In ActiveWorkbook.Worksheets
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="C:\Desktop\" & WS.Name, Quality:=xlQualityStandard, OpenAfterPublish:=False
    Next WS
End Sub

Hope that helps,
Cindy

 

[Cindy Ellis]

Oops...please ignore my previous post it creates output, but all the pdf's were the same (but with different file names). If your desktop is within "Documents and Settings\username" you will need to change the path or you'll get an error.
This should work:

Sub TestMakePdf()
Dim WS As Worksheet
    For Each WS In ActiveWorkbook.Worksheets
        WS.ExportAsFixedFormat Type:=xlTypePDF, FileName:="C:\Desktop\" & WS.Name, Quality:=xlQualityStandard, OpenAfterPublish:=False
    Next WS
End Sub

 

[judgepax]

WOW! That worked better than I was aiming for!! Thank you so much!! I now have a slick-as-snot cheat macro for a previously very labor-intense process.