Results 1 to 9 of 9

Thread: printing code needs edited
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2012
    Location
    Alabama
    Posts
    757
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default printing code needs edited

    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
    
    
    
    
    If PdfFilename <> False Then
       ' With ActiveSheet.PageSetup
    
        ActiveSheet.PageSetup.PrintArea = "$A$1:$K$49"
    
            .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
    
    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

  2. #2
    Board Regular
    Join Date
    Mar 2016
    Posts
    222
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: printing code needs edited

    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

  3. #3
    Board Regular
    Join Date
    Jan 2012
    Location
    Alabama
    Posts
    757
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: printing code needs edited

    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

  4. #4
    Board Regular
    Join Date
    Mar 2016
    Posts
    222
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: printing code needs edited

    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)
    Hobby: VBA and some .Net
    Career: the world of patent and trademark protection

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  5. #5
    Board Regular
    Join Date
    Jan 2012
    Location
    Alabama
    Posts
    757
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: printing code needs edited

    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

  6. #6
    Board Regular
    Join Date
    Mar 2016
    Posts
    222
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: printing code needs edited

    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.
    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.
    Hobby: VBA and some .Net
    Career: the world of patent and trademark protection

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  7. #7
    Board Regular
    Join Date
    Jan 2012
    Location
    Alabama
    Posts
    757
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: printing code needs edited

    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
        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

  8. #8
    Board Regular
    Join Date
    Mar 2016
    Posts
    222
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: printing code needs edited

    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".
    Hobby: VBA and some .Net
    Career: the world of patent and trademark protection

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  9. #9
    Board Regular
    Join Date
    Jan 2012
    Location
    Alabama
    Posts
    757
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: printing code needs edited

    Thanks for the help

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •