printing code needs edited

B

bamaisgreat

Well-known Member
Joined
Jan 23, 2012
Messages
831
Office Version
  1. 365
Platform
  1. Windows
As you can tell the code below prints of the sheets that contain data in them. The trouble im having is about half way down there are some print settings that are only working on the active sheet. Im unsure what to replace the active sheet with so it will apply all the printer settings listed to all pages that are needed.

Code: 
Sub SaveSpecificToPDF()
    
Dim PdfFilename As Variant
Dim ws As Worksheet
'Dim TransNO As String
'Dim fName As String
Dim TransRange As String: TransRange = ActiveSheet.Range("B22").Text


'Old code segment
'fName = ActiveSheet.Range("B21").Value


PdfFilename = Application.GetSaveAsFilename( _
    InitialFileName:="GGS Transmittal ", _
    FileFilter:="PDF, *.pdf", _
    Title:="Save As PDF")


    For Each ws In ThisWorkbook.Worksheets
        ws.Visible = xlSheetVisible
        Next ws


'If statements to select number of sheets to print in transmittal
If ActiveWorkbook.Sheets("Transmittal Generator").Range("F3") = IsEmpty("F3") Then
    MsgBox "Nothing to print. Verify Transmittal Generator has been properly filled out."
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10", "Trans Sh 11", "Trans Sh 12")).Visible = xlSheetHidden
    Exit Sub
        
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F17") = IsEmpty("F17") Then
    Sheets(Array("Trans Sh 1")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F31") = IsEmpty("F31") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F45") = IsEmpty("F45") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F59") = IsEmpty("F59") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F73") = IsEmpty("F73") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F87") = IsEmpty("F87") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F101") = IsEmpty("F101") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F115") = IsEmpty("F115") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F129") = IsEmpty("F129") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F143") = IsEmpty("F143") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F157") = IsEmpty("F157") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10", "Trans Sh 11")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F171") = IsEmpty("F171") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10", "Trans Sh 11", "Trans Sh 12")).Select
    
    
    End If




[B]If PdfFilename <> False Then[/B]
[B]   ' With ActiveSheet.PageSetup[/B]

[B]    ActiveSheet.PageSetup.PrintArea = "$A$1:$K$49"[/B]

[B]        .Orientation = xlPortrait[/B]
[B]        .PrintArea = "$A$1:$K$49"[/B]
[B]         LeftMargin = Application.InchesToPoints(0.25)[/B]
[B]        .RightMargin = Application.InchesToPoints(0.25)[/B]
[B]        .TopMargin = Application.InchesToPoints(0.5)[/B]
[B]        .BottomMargin = Application.InchesToPoints(0.5)[/B]
[B]        .HeaderMargin = Application.InchesToPoints(0.5)[/B]
[B]        .FooterMargin = Application.InchesToPoints(0.5)[/B]
[B]        .CenterHorizontally = True[/B]
[B]        .CenterVertically = False[/B]
[B]        .Zoom = False[/B]
[B]        .FitToPagesTall = 1[/B]
[B]        .FitToPagesWide = 1[/B]

[B]        .PaperSize = xlPaperLetter[/B]
[B]    End With[/B]

[B]ActiveSheet.ExportAsFixedFormat _[/B]
[B]    Type:=xlTypePDF, _[/B]
[B]    FileName:=PdfFilename, _[/B]
[B]    Quality:=xlQualityStandard, _[/B]
[B]    IncludeDocProperties:=False, _[/B]
[B]    IgnorePrintAreas:=False, _[/B]
[B]    From:=1, _[/B]
[B]    To:=12, _[/B]
[B]    OpenAfterPublish:=True[/B]
    
End If


  For Each ws In ThisWorkbook.Worksheets
    If Left(ws.Name, 6) = "Trans " Then
    ws.Visible = xlSheetHidden
    
    End If
    
    Next ws
    
End Sub
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
How about this:

Code: 
Sub SaveSpecificToPDF()
    Dim PdfFilename As Variant
    Dim ws As Worksheet
    'Dim TransNO As String
    'Dim fName As String
    Dim TransRange As String: TransRange = ActiveSheet.Range("B22").Text


    'Old code segment
    'fName = ActiveSheet.Range("B21").Value


    PdfFilename = Application.GetSaveAsFilename( _
        InitialFileName:="GGS Transmittal ", _
        FileFilter:="PDF, *.pdf", _
        Title:="Save As PDF")


    For Each ws In ThisWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws


    'If statements to select number of sheets to print in transmittal
    If ActiveWorkbook.Sheets("Transmittal Generator").Range("F3") = IsEmpty("F3") Then
        MsgBox "Nothing to print. Verify Transmittal Generator has been properly filled out."
        Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10", "Trans Sh 11", "Trans Sh 12")).Visible = xlSheetHidden
        Exit Sub
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F17") = IsEmpty("F17") Then
        Sheets(Array("Trans Sh 1")).Select
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F31") = IsEmpty("F31") Then
        Sheets(Array("Trans Sh 1", "Trans Sh 2")).Select
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F45") = IsEmpty("F45") Then
        Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3")).Select
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F59") = IsEmpty("F59") Then
        Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4")).Select
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F73") = IsEmpty("F73") Then
        Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5")).Select
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F87") = IsEmpty("F87") Then
        Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6")).Select
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F101") = IsEmpty("F101") Then
        Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7")).Select
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F115") = IsEmpty("F115") Then
        Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8")).Select
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F129") = IsEmpty("F129") Then
        Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9")).Select
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F143") = IsEmpty("F143") Then
        Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10")).Select
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F157") = IsEmpty("F157") Then
        Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10", "Trans Sh 11")).Select
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F171") = IsEmpty("F171") Then
        Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10", "Trans Sh 11", "Trans Sh 12")).Select
    End If


    If PdfFilename <> False Then
        For Each ws In ActiveWindow.SelectedSheets
            With ws.PageSetup
                .Orientation = xlPortrait
                .PrintArea = "$A$1:$K$49"
                 LeftMargin = Application.InchesToPoints(0.25)
                .RightMargin = Application.InchesToPoints(0.25)
                .TopMargin = Application.InchesToPoints(0.5)
                .BottomMargin = Application.InchesToPoints(0.5)
                .HeaderMargin = Application.InchesToPoints(0.5)
                .FooterMargin = Application.InchesToPoints(0.5)
                .CenterHorizontally = True
                .CenterVertically = False
                .Zoom = False
                .FitToPagesTall = 1
                .FitToPagesWide = 1
                .PaperSize = xlPaperLetter
            End With
            ws.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=PdfFilename, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=False, _
                IgnorePrintAreas:=False, _
                From:=1, _
                To:=12, _
                OpenAfterPublish:=True
        Next ws
    End If


    For Each ws In ThisWorkbook.Worksheets
        If Left(ws.Name, 6) = "Trans " Then
            ws.Visible = xlSheetHidden
        End If
    Next ws
End Sub
 
Upvote 0
The problem Im having is that the margins are very wide. What part of the code could I edit to fix this issue? Thanks

Code: 
Sub SaveSpecificToPDF()
    
Dim PdfFilename As Variant
Dim ws As Worksheet
'Dim TransNO As String
'Dim fName As String
Dim TransRange As String: TransRange = ActiveSheet.Range("B22").Text


'Old code segment
'fName = ActiveSheet.Range("B21").Value


PdfFilename = Application.GetSaveAsFilename( _
    InitialFileName:="GGS Transmittal ", _
    FileFilter:="PDF, *.pdf", _
    Title:="Save As PDF")


    For Each ws In ThisWorkbook.Worksheets
        ws.Visible = xlSheetVisible
        Next ws


'If statements to select number of sheets to print in transmittal
If ActiveWorkbook.Sheets("Transmittal Generator").Range("F3") = IsEmpty("F3") Then
    MsgBox "Nothing to print. Verify Transmittal Generator has been properly filled out."
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10", "Trans Sh 11", "Trans Sh 12")).Visible = xlSheetHidden
    Exit Sub
        
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F17") = IsEmpty("F17") Then
    Sheets(Array("Trans Sh 1")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F31") = IsEmpty("F31") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F45") = IsEmpty("F45") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F59") = IsEmpty("F59") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F73") = IsEmpty("F73") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F87") = IsEmpty("F87") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F101") = IsEmpty("F101") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F115") = IsEmpty("F115") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F129") = IsEmpty("F129") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F143") = IsEmpty("F143") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F157") = IsEmpty("F157") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10", "Trans Sh 11")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F171") = IsEmpty("F171") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10", "Trans Sh 11", "Trans Sh 12")).Select
    
    
    End If
    
'Old code segment
'Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4")).Select


If PdfFilename <> False Then
    With ActiveSheet.PageSetup
        
        .Orientation = xlPortrait
        .PrintArea = "$A$1:$K$49"
 
        .Zoom = False
        .FitToPagesTall = False
        .FitToPagesWide = False
        
        .PaperSize = xlPaperLetter
    End With


ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    FileName:=PdfFilename, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=False, _
    IgnorePrintAreas:=False, _
    From:=1, _
    To:=12, _
    OpenAfterPublish:=True
    
End If


  For Each ws In ThisWorkbook.Worksheets
    If Left(ws.Name, 6) = "Trans " Then
    ws.Visible = xlSheetHidden
    
    End If
    
    Next ws
    
End Sub
 
Upvote 0
Put this after the .PaperSize = xlPaperLetter code. Change the numbers to what you need.

Code: 
.LeftMargin = Application.InchesToPoints(0.2)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
 
Upvote 0
Thank you for the help.
It is working good now except it is only applying the settings to the first sheet. Could you see where I need to edit? As you can probably tell It only prints the sheets that have data in a certain range.

Code: 
Sub SaveSpecificToPDF()
    
Dim PdfFilename As Variant
Dim ws As Worksheet
'Dim TransNO As String
'Dim fName As String
Dim TransRange As String: TransRange = ActiveSheet.Range("B22").Text


'Old code segment
'fName = ActiveSheet.Range("B21").Value


PdfFilename = Application.GetSaveAsFilename( _
    InitialFileName:="GGS Transmittal ", _
    FileFilter:="PDF, *.pdf", _
    Title:="Save As PDF")


    For Each ws In ThisWorkbook.Worksheets
        ws.Visible = xlSheetVisible
        Next ws


'If statements to select number of sheets to print in transmittal
If ActiveWorkbook.Sheets("Transmittal Generator").Range("F3") = IsEmpty("F3") Then
    MsgBox "Nothing to print. Verify Transmittal Generator has been properly filled out."
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10", "Trans Sh 11", "Trans Sh 12")).Visible = xlSheetHidden
    Exit Sub
        
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F17") = IsEmpty("F17") Then
    Sheets(Array("Trans Sh 1")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F31") = IsEmpty("F31") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F45") = IsEmpty("F45") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F59") = IsEmpty("F59") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F73") = IsEmpty("F73") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F87") = IsEmpty("F87") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F101") = IsEmpty("F101") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F115") = IsEmpty("F115") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F129") = IsEmpty("F129") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F143") = IsEmpty("F143") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F157") = IsEmpty("F157") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10", "Trans Sh 11")).Select
    
    ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F171") = IsEmpty("F171") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10", "Trans Sh 11", "Trans Sh 12")).Select
    
    
    End If
    
'Old code segment
'Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4")).Select


If PdfFilename <> False Then
    With ActiveSheet.PageSetup
        
        .Orientation = xlPortrait
        .PrintArea = "$A$1:$K$49"
 
        .Zoom = False
        .FitToPagesTall = 1
        .FitToPagesWide = 1
        
        .PaperSize = xlPaperLetter
        .LeftMargin = Application.InchesToPoints(0.2)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        
    End With


ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    FileName:=PdfFilename, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=False, _
    IgnorePrintAreas:=False, _
    From:=1, _
    To:=12, _
    OpenAfterPublish:=True
    
End If


  For Each ws In ThisWorkbook.Worksheets
    If Left(ws.Name, 6) = "Trans " Then
    ws.Visible = xlSheetHidden
    
    End If
    
    Next ws
    
End Sub
 
Upvote 0
Since you're printing pages 1 to 12 with the ActiveSheet.ExportAsFixedFormat command, I added a For loop to change the page setup of sheets 1 to 12.
Code: 
Sub SaveSpecificToPDF()
Dim PdfFilename As Variant
Dim ws As Worksheet
'Dim TransNO As String
'Dim fName As String
Dim TransRange As String: TransRange = ActiveSheet.Range("B22").Text

'Old code segment
'fName = ActiveSheet.Range("B21").Value

PdfFilename = Application.GetSaveAsFilename( _
    InitialFileName:="GGS Transmittal ", _
    FileFilter:="PDF, *.pdf", _
    Title:="Save As PDF")

    For Each ws In ThisWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws

'If statements to select number of sheets to print in transmittal
If ActiveWorkbook.Sheets("Transmittal Generator").Range("F3") = IsEmpty("F3") Then
    MsgBox "Nothing to print. Verify Transmittal Generator has been properly filled out."
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10", "Trans Sh 11", "Trans Sh 12")).Visible = xlSheetHidden
    Exit Sub

ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F17") = IsEmpty("F17") Then
    Sheets(Array("Trans Sh 1")).Select

ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F31") = IsEmpty("F31") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2")).Select

ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F45") = IsEmpty("F45") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3")).Select

ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F59") = IsEmpty("F59") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4")).Select

ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F73") = IsEmpty("F73") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5")).Select

ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F87") = IsEmpty("F87") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6")).Select

ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F101") = IsEmpty("F101") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7")).Select

ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F115") = IsEmpty("F115") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8")).Select

ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F129") = IsEmpty("F129") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9")).Select

ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F143") = IsEmpty("F143") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10")).Select

ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F157") = IsEmpty("F157") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10", "Trans Sh 11")).Select

ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F171") = IsEmpty("F171") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10", "Trans Sh 11", "Trans Sh 12")).Select

End If
    
'Old code segment
'Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4")).Select

If PdfFilename <> False Then
    Dim i As Integer
    For i = 1 To 12
        With ActiveWorkbook.Sheets(i).PageSetup
            
            .Orientation = xlPortrait
            .PrintArea = "$A$1:$K$49"
            
            .Zoom = False
            .FitToPagesTall = 1
            .FitToPagesWide = 1
            
            .PaperSize = xlPaperLetter
            .LeftMargin = Application.InchesToPoints(0.2)
            .RightMargin = Application.InchesToPoints(0.25)
            .TopMargin = Application.InchesToPoints(0.5)
            .BottomMargin = Application.InchesToPoints(0.5)
            
        End With
    Next ws

    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=PdfFilename, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, _
        From:=1, _
        To:=12, _
        OpenAfterPublish:=True
    
End If

  For Each ws In ThisWorkbook.Worksheets
    If Left(ws.Name, 6) = "Trans " Then
    ws.Visible = xlSheetHidden
    
    End If
    
    Next ws
    
End Sub
As you can probably tell It only prints the sheets that have data in a certain range.
Click to expand...
I can't tell, actually. I'm not sure what the point is of selecting the different sheets with the If block if you're just exporting pages 1 to 12 anyway.
 
Upvote 0
Im getting a Compile Error: Invalid Next control variable reference. I highlighed it in red in the code below

Code: 
Sub SaveSpecificToPDF()
Dim PdfFilename As Variant
Dim ws As Worksheet
'Dim TransNO As String
'Dim fName As String
Dim TransRange As String: TransRange = ActiveSheet.Range("B22").Text


'Old code segment
'fName = ActiveSheet.Range("B21").Value


PdfFilename = Application.GetSaveAsFilename( _
    InitialFileName:="GGS Transmittal ", _
    FileFilter:="PDF, *.pdf", _
    Title:="Save As PDF")


    For Each ws In ThisWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws


'If statements to select number of sheets to print in transmittal
If ActiveWorkbook.Sheets("Transmittal Generator").Range("F3") = IsEmpty("F3") Then
    MsgBox "Nothing to print. Verify Transmittal Generator has been properly filled out."
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10", "Trans Sh 11", "Trans Sh 12")).Visible = xlSheetHidden
    Exit Sub


ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F17") = IsEmpty("F17") Then
    Sheets(Array("Trans Sh 1")).Select


ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F31") = IsEmpty("F31") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2")).Select


ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F45") = IsEmpty("F45") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3")).Select


ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F59") = IsEmpty("F59") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4")).Select


ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F73") = IsEmpty("F73") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5")).Select


ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F87") = IsEmpty("F87") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6")).Select


ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F101") = IsEmpty("F101") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7")).Select


ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F115") = IsEmpty("F115") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8")).Select


ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F129") = IsEmpty("F129") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9")).Select


ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F143") = IsEmpty("F143") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10")).Select


ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F157") = IsEmpty("F157") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10", "Trans Sh 11")).Select


ElseIf ActiveWorkbook.Sheets("Transmittal Generator").Range("F171") = IsEmpty("F171") Then
    Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4", "Trans Sh 5", "Trans Sh 6", "Trans Sh 7", "Trans Sh 8", "Trans Sh 9", "Trans Sh 10", "Trans Sh 11", "Trans Sh 12")).Select


End If
    
'Old code segment
'Sheets(Array("Trans Sh 1", "Trans Sh 2", "Trans Sh 3", "Trans Sh 4")).Select


If PdfFilename <> False Then
    Dim i As Integer
    For i = 1 To 12
        With ActiveWorkbook.Sheets(i).PageSetup
            
            .Orientation = xlPortrait
            .PrintArea = "$A$1:$K$49"
            
            .Zoom = False
            .FitToPagesTall = 1
            .FitToPagesWide = 1
            
            .PaperSize = xlPaperLetter
            .LeftMargin = Application.InchesToPoints(0.2)
            .RightMargin = Application.InchesToPoints(0.25)
            .TopMargin = Application.InchesToPoints(0.5)
            .BottomMargin = Application.InchesToPoints(0.5)
            
        End With
[COLOR=#ff0000]    Next ws[/COLOR]


    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=PdfFilename, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, _
        From:=1, _
        To:=12, _
        OpenAfterPublish:=True
    
End If


  For Each ws In ThisWorkbook.Worksheets
    If Left(ws.Name, 6) = "Trans " Then
    ws.Visible = xlSheetHidden
    
    End If
    
    Next ws
    
End Sub
 
Upvote 0
That's an oversight on my part. I had something else for the For loop involving ws in an earlier version. Change it to "Next i".
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
VBA code to reference worksheet second from the end
Replies
1
Views
199
John_w
J
J
  • Solved
Speeding up VBA
Replies
9
Views
154
jvoss
J
A
  • Solved
Could not set the RowSource property. Invalid property value. Error 380
2
Replies
16
Views
210
RoryA
RoryA
S
  • Solved
Problem with showing more than 10 columns on listbox
2
Replies
13
Views
944
dmt32
D
T
  • Question Question
VBA to run when a table with a SQL query is refreshed?
Replies
0
Views
179
Tarver
T

Forum statistics

Threads
1,223,908
Messages
6,175,307
Members
452,633
Latest member
DougMo

Share this page

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