Creating Loop that creates and saves specific sheets(tabs) to PDF

[dash11]

I'm trying to create a loop that will pdf selected sheets that are between sheets "Start" and "End". For some reason it will pdf the first tab but then it stops. Here is my current code:

Sub PDF_Converter()

Dim ws As Worksheet, aws As Worksheet
Dim StartIndex, EndIndex As Integer
Dim i As Integer
Set aws = ActiveSheet
MName = ActiveSheet.Name & ".pdf"
MDir = "U:\Dashboard\Jaime Bailey"
StartIndex = Sheets("Start").Index + 1
EndIndex = Sheets("End").Index - 1
Application.ScreenUpdating = False

Sheets("Start").Next.Select
For i = StartIndex To EndIndex


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=MDir & "\" & MName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

Next i

End Sub

[evertjvr]

You need to change the sheet and use the i inside you your loop to identify the sheet to be exported



PS: Remember to use the [code] tag when quoting code.

[Chippy]

Instead of ActiveSheet.ExportAsFixedFormat....

Please Login or Register  to view this content.

[dash11]

Thanks Chippy but for some reason it's still only saving out the first tab within the parameters. For some reason it's not looping back through and doing the other following tabs. Here is what I have after using your last update:

Sub PDF_Converter()

Dim StartIndex, EndIndex As Integer
Dim i As Integer
MName = ActiveSheet.Name & ".pdf"
MDir = "U:\Dashboard\Jaime Bailey"
StartIndex = Sheets("Start").Index + 1
EndIndex = Sheets("End").Index - 1
Application.ScreenUpdating = False


For i = StartIndex To EndIndex


Sheets(i).ExportAsFixedFormat Type:=xlTypePDF, Filename:=MDir & "\" & MName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

Next i

End Sub

[evertjvr]

Cherpy has a good line there but try this one as addition to your original code

Please Login or Register  to view this content.

[tigeravatar]

dash11,

It is looping through and saving the sheets, the problem is that each time it saves, it is saving as the exact same name, so it just keeps overwriting the file. You need to put your MName line inside the loop:

Please Login or Register  to view this content.

[dash11]

Thanks All! Tiger hit this one on the head. I had a loop setup that was chasing it's own tail. Much appreciated gentlemen!