0 votes
in VBA by Beginner (3 points)
edited by

This is what I currently have, and works well since the users only had one worksheet.  Now I need to either create an array and loop through it, or a dynamic index to refer to, to select the sheets to print.  The value to check would be the same cell on all 5 workshets. These worksheets fill up from the first to the last.  So, there will never be the first sheet and third printed.  The count could be as little as the first sheet, or as much as all 5 sheets.  I would need this pdf to print in the order from first to last as well.  Below is the code that I have for the one sheet, that takes the contents of a cell reference as the save location, and creates the pdf and saves it there.  The code below is for taking totals from the worksheet and adding the fiscal numbers.  Any help will be appreciated.  I'm on a deadline for this, but havn't had much luck in googling a solution together.

Option Explicit

Sub SaveAsPDF()


Dim fName As String

ActiveSheet.Select
    fName = Range("H6").Value
   
    
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "S:\Customers\" & fName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True


Sheets("Fiscal_Calc").Select
Range("D13:I38").SpecialCells(xlCellTypeVisible).Select
Selection.Copy

Sheets("Quote_Log").Select
Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Sheets("Quote").Select
 
            
 
End Sub

 

2 Answers

+1 vote
by Beginner (56 points)

as you do not specify the criteria you will have to edit the generic line i put in

Sub SaveAsPDF()


Dim fName As String, ws As Integer

'ActiveSheet.Select
   
For ws = 1 To wb.Sheets.Count
    If Sheets(ws).Range(somecell) = somecriteria Then
        fName = Sheets(ws).Range("H6").Value
        Sheets(ws).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                    "S:\Customers\" & fName, Quality:=xlQualityStandard, _
                    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
        
        Sheets("Fiscal_Calc").Select
        Sheets(ws).Range("D13:I38").SpecialCells(xlCellTypeVisible).Select
        Selection.Copy
        
        Sheets("Quote_Log").Select
        Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
    End If
Next ws

Sheets("Quote").Select

End Sub

and i guessed a few other things

by Beginner (3 points)
Thank you Westcon1,

 

I actually have about 60 worksheets in this workbook, so how would I define that I only want this to pertain to the sheet names Quote, Quote2, Quote3, Quote4 and Quote5.  If any of these sheets has a value greater than 0 in cell H1, I want them included in the pdf save.
+1 vote
by Skilled (485 points)

Hi darkgyft,

I created something similar for another user who wanted to conditionally print worksheets. I merged your code with mine. Since you have 60 worksheets, it might be slow. The way it handles worksheets, if you ever realize that you may need to skip sheets (if they stop filling up from first to last), it will still work.

Excel automatically prints all visible sheets to PDF, so a loop won't work, unless you want a separate PDF for each sheet. In your case, you need to hide the sheets you don't want to print.

One assumption I made is that you call your routine from the Active sheet.
 

Option Explicit

    ' Targets is a variable holding all of the worksheets to be printed
    ' The DEFAULT BEHAVIOR of ToggleVisible is to hide sheets that do NOT appear in the Targets variable.
    
    Dim Targets As String
    

Sub SaveAsPDF()
    
    Dim ws As Worksheet
    Dim fName As String
    
    ActiveSheet.Select
    fName = Range("H6").Value
    Targets = ""
    For Each ws In ThisWorkbook.Worksheets
        'Debug.Print ws.Name, ws.Range("H1")
        If ws.Range("H1") > 0 Then
            Targets = Targets & UCase(ws.Name) & ","
        End If
    Next
        
    If Targets = "" Then
        Exit Sub
    End If
    
    ' Hide unwanted worksheets during printing
    ToggleVisible False

    ' Don't stop macro if there is nothing to print
    On Error Resume Next
    
    ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "exported file.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
    
    ' Reset error handling
    On Error GoTo 0
    
    ' Unhide hidden worksheets
    ToggleVisible True

    ' Perform other post-printing operations
    
    Sheets("Fiscal_Calc").Select
    Range("D13:I38").SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    
    Sheets("Quote_Log").Select
    Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    
    Sheets("Quote").Select

End Sub

Private Sub ToggleVisible(state As Boolean)
    
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Sheets
        With ws
            ' Comment out next line if you WANT to see page breaks
            .DisplayPageBreaks = False
            If InStr(Targets, UCase(.Name)) = 0 Then
                .Visible = state
            End If
        End With
    Next ws
    
End Sub

Cheers,

 

Mitch

 

by Beginner (3 points)
Thanks so much Mitch,

I think that this code will work for what I need it to do.  I was just wondering if I make the 5 worksheets part of a collection, how would I iterate through the collection instead?
by Skilled (485 points)
Hi darkgyft,

I've been hunting around on Google and have not found a 100% VBA answer. The manual way is to SHIFT+Click each sheet and use the built-in SaveAs menu.

One additional idea, though it may be serious overkill, is to first export the sheets to a new workbook and print the new workbook to PDF. I have no idea why you would do that, except to avoid a painfully slow hide-print-unhide loop.

Cheers,

 

Mitch
by Beginner (56 points)
i did something like this previously, just created a temp sheet and added the sheets to be printed to that, then print, could resize it so that several sheets could fit on a page
by Beginner (56 points)

to just look at the specific sheets, you could make a loop like

For sh = 1 To 5
    If Sheets(Replace("quote" & sh, "1", "")).Range("h1").Value > 0 Then
        ' print this sheet
    End If
Next

 

by Skilled (485 points)

@Westconn1,

Excel doesn't work that way, if you'r trying to put all of the sheets into a single PDF. I used to think it should work like that , too. But when you examine the

.ExportAsFixedFormat

method, you find that it operates at the workbook level.

The ExportAsFixedFormat method is used to publish a workbook to either the PDF or XPS format.
Here is the link to the Microsoft Knowledgebase article: [link]
 
This is why we have to hide worksheets.
 
Cheers,
 
Mitch
 
by Skilled (485 points)

Hi Westconn1,

Did you know that you could drag the PrintArea lines to the right and to the bottom? This gives you more columns per page, along with resizing columns, using narrow margins and Zoom (out).

laugh

Welcome to wellsr Q&A
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...