save excel sheets as pdf via vba

[daveyc18]

i need to re-arrange the tabs so that it saves the PDF in that order....is there way to do this without manually moving the worksheets?

i tried re-arranging the sheet names in the vba array code, but that didnt seem to do the trickl

 

[Logit]

.

[COLOR=#00007F][FONT=&quot]Sub[/FONT][/COLOR][COLOR=#403F41][FONT=&quot] Reorder_Sheets()[/FONT][/COLOR]

[COLOR=#007F00][FONT=&quot]'PURPOSE: Order Worksheets in a custom way (works even if some of the worksheets are missing)[/FONT][/COLOR]
[COLOR=#007F00][FONT=&quot]'SOURCE: www.TheSpreadsheetGuru.com[/FONT][/COLOR]

[COLOR=#00007F][FONT=&quot]Dim[/FONT][/COLOR][COLOR=#403F41][FONT=&quot] x [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]As[/FONT][/COLOR][COLOR=#403F41][FONT=&quot] [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]Long[/FONT][/COLOR]
[COLOR=#00007F][FONT=&quot]Dim[/FONT][/COLOR][COLOR=#403F41][FONT=&quot] myOrder [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]As[/FONT][/COLOR][COLOR=#403F41][FONT=&quot] [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]Variant[/FONT][/COLOR]

[COLOR=#403F41][FONT=&quot]Application.DisplayAlerts = [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]False[/FONT][/COLOR]
[COLOR=#403F41][FONT=&quot]Application.ScreenUpdating = [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]False[/FONT][/COLOR]

[COLOR=#403F41][FONT=&quot]myOrder = Array("Sheet1", "Sheet4", "Sheet3", "Sheet6", "Sheet9", "Sheet11", "Sheet10", "Sheet5")[/FONT][/COLOR]

[COLOR=#00007F][FONT=&quot]On[/FONT][/COLOR][COLOR=#403F41][FONT=&quot] [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]Error[/FONT][/COLOR][COLOR=#403F41][FONT=&quot] [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]Resume[/FONT][/COLOR][COLOR=#403F41][FONT=&quot] [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]Next[/FONT][/COLOR]
[COLOR=#403F41][FONT=&quot]  [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]For[/FONT][/COLOR][COLOR=#403F41][FONT=&quot] x = [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]UBound[/FONT][/COLOR][COLOR=#403F41][FONT=&quot](myOrder) [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]To[/FONT][/COLOR][COLOR=#403F41][FONT=&quot] [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]LBound[/FONT][/COLOR][COLOR=#403F41][FONT=&quot](myOrder) [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]Step[/FONT][/COLOR][COLOR=#403F41][FONT=&quot] -1[/FONT][/COLOR]
[COLOR=#403F41][FONT=&quot]    Worksheets(myOrder(x)).Move Before:=Worksheets(1)[/FONT][/COLOR]
[COLOR=#403F41][FONT=&quot]  [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]Next[/FONT][/COLOR][COLOR=#403F41][FONT=&quot] x[/FONT][/COLOR]
[COLOR=#00007F][FONT=&quot]On[/FONT][/COLOR][COLOR=#403F41][FONT=&quot] [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]Error[/FONT][/COLOR][COLOR=#403F41][FONT=&quot] [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]GoTo[/FONT][/COLOR][COLOR=#403F41][FONT=&quot] 0[/FONT][/COLOR]

[COLOR=#403F41][FONT=&quot]Application.DisplayAlerts = [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]True[/FONT][/COLOR]
[COLOR=#403F41][FONT=&quot]Application.ScreenUpdating = [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]True[/FONT][/COLOR]

[COLOR=#00007F][FONT=&quot]End[/FONT][/COLOR][COLOR=#403F41][FONT=&quot] [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]Sub[/FONT][/COLOR]

Arrange sheets in order desired in the ARRAY statement. Save the workbook. Then convert to PDF.
Re-arrange and save afterwards if desired.

https://www.thespreadsheetguru.com/the-code-vault/2014/4/7/custom-order-of-worksheet-positions

 

[daveyc18]

.

[COLOR=#00007F][FONT='inherit']Sub[/FONT][/COLOR][COLOR=#403F41][FONT='inherit'] Reorder_Sheets()[/FONT][/COLOR]

[COLOR=#007F00][FONT='inherit']'PURPOSE: Order Worksheets in a custom way (works even if some of the worksheets are missing)[/FONT][/COLOR]
[COLOR=#007F00][FONT='inherit']'SOURCE: www.TheSpreadsheetGuru.com[/FONT][/COLOR]

[COLOR=#00007F][FONT='inherit']Dim[/FONT][/COLOR][COLOR=#403F41][FONT='inherit'] x [/FONT][/COLOR][COLOR=#00007F][FONT='inherit']As[/FONT][/COLOR][COLOR=#00007F][FONT='inherit']Long[/FONT][/COLOR]
[COLOR=#00007F][FONT='inherit']Dim[/FONT][/COLOR][COLOR=#403F41][FONT='inherit'] myOrder [/FONT][/COLOR][COLOR=#00007F][FONT='inherit']As[/FONT][/COLOR][COLOR=#00007F][FONT='inherit']Variant[/FONT][/COLOR]

[COLOR=#403F41][FONT='inherit']Application.DisplayAlerts = [/FONT][/COLOR][COLOR=#00007F][FONT='inherit']False[/FONT][/COLOR]
[COLOR=#403F41][FONT='inherit']Application.ScreenUpdating = [/FONT][/COLOR][COLOR=#00007F][FONT='inherit']False[/FONT][/COLOR]

[COLOR=#403F41][FONT='inherit']myOrder = Array("Sheet1", "Sheet4", "Sheet3", "Sheet6", "Sheet9", "Sheet11", "Sheet10", "Sheet5")[/FONT][/COLOR]

[COLOR=#00007F][FONT='inherit']On[/FONT][/COLOR][COLOR=#00007F][FONT='inherit']Error[/FONT][/COLOR][COLOR=#00007F][FONT='inherit']Resume[/FONT][/COLOR][COLOR=#00007F][FONT='inherit']Next[/FONT][/COLOR]
[COLOR=#00007F][FONT='inherit']For[/FONT][/COLOR][COLOR=#403F41][FONT='inherit'] x = [/FONT][/COLOR][COLOR=#00007F][FONT='inherit']UBound[/FONT][/COLOR][COLOR=#403F41][FONT='inherit'](myOrder) [/FONT][/COLOR][COLOR=#00007F][FONT='inherit']To[/FONT][/COLOR][COLOR=#00007F][FONT='inherit']LBound[/FONT][/COLOR][COLOR=#403F41][FONT='inherit'](myOrder) [/FONT][/COLOR][COLOR=#00007F][FONT='inherit']Step[/FONT][/COLOR][COLOR=#403F41][FONT='inherit'] -1[/FONT][/COLOR]
[COLOR=#403F41][FONT='inherit']    Worksheets(myOrder(x)).Move Before:=Worksheets(1)[/FONT][/COLOR]
[COLOR=#00007F][FONT='inherit']Next[/FONT][/COLOR][COLOR=#403F41][FONT='inherit'] x[/FONT][/COLOR]
[COLOR=#00007F][FONT='inherit']On[/FONT][/COLOR][COLOR=#00007F][FONT='inherit']Error[/FONT][/COLOR][COLOR=#00007F][FONT='inherit']GoTo[/FONT][/COLOR][COLOR=#403F41][FONT='inherit'] 0[/FONT][/COLOR]

[COLOR=#403F41][FONT='inherit']Application.DisplayAlerts = [/FONT][/COLOR][COLOR=#00007F][FONT='inherit']True[/FONT][/COLOR]
[COLOR=#403F41][FONT='inherit']Application.ScreenUpdating = [/FONT][/COLOR][COLOR=#00007F][FONT='inherit']True[/FONT][/COLOR]

[COLOR=#00007F][FONT='inherit']End[/FONT][/COLOR][COLOR=#00007F][FONT='inherit']Sub[/FONT][/COLOR]

Arrange sheets in order desired in the ARRAY statement. Save the workbook. Then convert to PDF.
Re-arrange and save afterwards if desired.

https://www.thespreadsheetguru.com/the-code-vault/2014/4/7/custom-order-of-worksheet-positions

excellent, that worked..very creative.

 

[Logit]

.
You are welcome.