vba print pdf files for specific list of sheets

Question

Is it possible to modify this code to print a few pdf files for a specific list of sheets?

    Sub printselection()
        Dim rng As Range
        Dim wks As Worksheet
        Dim arr() As String
        Dim i As Long: i = 0
        Dim x As Integer

        For x = 1 To 3
        For Each rng In Sheets("Consolidated").Range(Cells(x, x), Cells(x, x))

            If Trim(rng.Value) <> "" Then
                On Error Resume Next
                Set wks = Nothing
                Set wks = Sheets(rng.Value)
                On Error GoTo 0
                If wks Is Nothing Then
                    MsgBox "Sheet " & rng.Value & " does not exist"
                Else
                    ReDim Preserve arr(i)
                    arr(i) = wks.Name
                    i = i + 1
                End If
            End If
        Next rng
        Dim printSheets As Variant
        printSheets = arr
        Worksheets(printSheets).PrintOut Preview:=False, ActivePrinter:="Adobe PDF", PrintToFile:=True, PrToFileName:=PSFileName
    Next x

 End Sub

I am learning as I go with vba.....it works for just column A and gets hung up on the last line and was hoping it would ask for a file name as it moves to the next column X. Here is a sample of column sheet names ( I want to print each column of sheet names to a different pdf file)

       A                    B                   C
CA 10 - 50 Area 1   CO 10 - 50 Area 1   GA 10 - 50 Area 1
CA 10 - 50 Area 2   CO 10 - 50 Area 2   GA 10 - 50 Area 2
CA 10 - 50 Area 3   CO 10 - 50 Area 3   GA 10 - 50 Area 3
CA 10 - 50 Area 4       
CA 10 - 50 Area 5

(column A is CA, column B is CO and column C is GA)

Run a loop to array printSheets and run print command for each sheet in printSheets array. — Harun24hr
– Harun24hr, Commented May 8, 2018 at 15:13
Thank you. I am not sure exactly the syntax for that. I tried changing printSheets = arr to printSheets = arr(x) but that didn't work. — L Heintzelman
– L Heintzelman, Commented May 8, 2018 at 18:07

MrMonk · Accepted Answer · 2018-05-09 07:40:24Z

0

Try to loop for each sheet in your printSheets array:

For Each itm In printSheets
Worksheets(itm).PrintOut Preview:=False, ActivePrinter:="Adobe PDF", PrintToFile:=True, PrToFileName:=PSFileName

Next itm

answered May 9, 2018 at 7:40

MrMonk

364 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

L Heintzelman Over a year ago

Thank you. I modified the code a bit and this works without error. This almost works the way I want. I need help with 2 or 3 more changes. 1. It does ask me to save 3 files which is what I want but after x=1, I want to clear the array before moving to x=2. How do I clear the "cache" for the array after saving the file to pdf? 2. Is there a simple way to change to code from saving to pdf to an excel file instead? 3. (this would be more nice to have) Automate naming of the files using a column/list of file names in a specific column in excel?

L Heintzelman Over a year ago

For x = 1 To 3 For Each rng In Sheets("Groups").Range(Cells(5, x), Cells(8, x)) Set wks = Sheets(rng.Value) ReDim Preserve arr(i) arr(i) = wks.Name i = i + 1 Next rng Dim printSheets As Variant printSheets = arr Worksheets(printSheets).PrintOut Preview:=False, ActivePrinter:="Adobe PDF", PrintToFile:=True, PrToFileName:=PSFileName Next x

Collectives™ on Stack Overflow

vba print pdf files for specific list of sheets

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related