Trying to get multiple worksheets to print in particular order

[andyk1968]

I have a workbook with multiple sheets.

The first sheet is a summary, and the following sheets are used for adding new data.

I have a macro to convert one data sheet and the summary sheet to a pdf.

I would like the data sheet to be first, and the summary to follow, but I can't seem to get this to work.

Here's the code:

<code>Sub PrintCurrentSheetSummary()
Dim Current As String
Dim SheetIn As Integer
Dim SheetName As String
Dim NewFilename As String
Dim Path As String
Dim Filepath As String

NewFilename = "CO " & Cells(3, 1).Value & " MBE-WBE Worksheet and Summary"  ' the creates the new pdf file name
    Path = Cells(1, 14).Value ' the path is dependent on where the excel file is saved
    Filepath = Path + NewFilename + ".pdf"

SheetIn = ActiveSheet.Index
SheetName = Sheets(SheetIn).Name



Sheets(Array(SheetName, "Sheet1")).Select

'Sheets("Sheet1").Move after:=Sheets(SheetIn)

' this saves the pdf
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        Filepath, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True

'Sheets("Sheet1").Move before:=Sheets("MBE WBE SBE")
End Sub

</code>
Thanks!

 

[John_w]

Try FitToPagesTall = False. Complete code:

Public Sub PrintCurrentSheetSummary3()

    Dim NewFilename As String
    Dim Path As String
    Dim Filepath As String
    Dim tempWb As Workbook
    
    With ActiveSheet
        NewFilename = "CO " & .Range("A3").Value & " MBE-WBE Worksheet and Summary.pdf"  'PDF file name
        Path = .Range("N1").Value                                                        'path where PDF will be created
    End With
    Filepath = Path & NewFilename

    Application.ScreenUpdating = False
    
    With ThisWorkbook
        Set tempWb = Workbooks.Add(xlWBATWorksheet)
        .ActiveSheet.Copy Before:=tempWb.Worksheets(1)
        .Worksheets("Sheet1").Range("exportpdf").Copy '.Worksheets("Sheet1").UsedRange.Copy
        tempWb.Worksheets(2).Range("A1").PasteSpecial xlPasteFormats
        tempWb.Worksheets(2).Range("A1").PasteSpecial xlPasteColumnWidths
        tempWb.Worksheets(2).Range("A1").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        With tempWb.Worksheets(2).PageSetup
            .Zoom = False
            .FitToPagesTall = False
            .FitToPagesWide = 1
        End With
        Application.CutCopyMode = False
        tempWb.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Filepath, _
            Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        tempWb.Close False
        .ActiveSheet.Select True
    End With
    
    Application.ScreenUpdating = True
    
    MsgBox "Created " & Filepath
    
End Sub

 

[andyk1968]

brilliant! Thanks.