×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Error 9 - Subscript out of range selecting sheets from UserForm
3

Error 9 - Subscript out of range selecting sheets from UserForm

Error 9 - Subscript out of range selecting sheets from UserForm

(OP)
Hello All,
I have a form that allows the users to select (via checkbox) which sheets they would like to combine into one pdf output. As my title says I am getting an "Error 9 - subscript out of range" error. Any help would be greatly appreciated.

CODE -->

Private Sub cmdPDF_Click()
    'On Error Resume Next

    Dim strPDF As String
    Dim ClientName As String
    Dim ClientFilename As Variant

    ClientName = Range("Client_First") & Range("Client_LasT")
    
    'Non Reg Client
    If Me.chkNonRegClient = True Then
        strPDF = strPDF & """Non_Reg_Client""" & ", "
    End If
    'Non Reg Spouse
    If Me.chkNonRegSpouse = True Then
        strPDF = strPDF & """Non_Reg_Spouse""" & ", "
    End If
    'TFSA Client
    If Me.chkTFSAClient = True Then
        strPDF = strPDF & """TFSA_Client""" & ", "
    End If
    'TFSA Spouse
    If Me.chkTFSASpouse = True Then
        strPDF = strPDF & """TFSA_Spouse""" & ", "
    End If
    'RRSP/RRIF Client
    If Me.chkRRSPRRIFClient = True Then
        strPDF = strPDF & """RRSP_RRIF_Client""" & ", "
    End If
    'RRSP/RRIF Spouse
    If Me.chkRRSPRRIFSpouse = True Then
        strPDF = strPDF & """RRSP_RRIF_Spouse""" & ", "
    End If
    'Locked/LIF Client
    If Me.chkLockedLIFClient = True Then
        strPDF = strPDF & """Locked_LIF_Client""" & ", "
    End If
    'Locked/LIF Spouse
    If Me.chkLokcedLIFSpouse = True Then
        strPDF = strPDF & """Locked_LIF_Spouse""" & ", "
    End If
    'Investment Account Summary
    If Me.chkIAS = True Then
        strPDF = strPDF & """InvestmentAccountSummary""" & ", "
    End If
    'Deposit/Withdrawal Summary
    If Me.chkDWDSummary = True Then
        strPDF = strPDF & """Dep_WD_Summary"""
    End If
    
    
    'Save selected worksheets as a PDF
    ClientFilename = Application.GetSaveAsFilename( _
    InitialFileName:=ClientName, _
    filefilter:="PDF, *.pdf", _
    Title:="Save As PDF")

    Sheets(Array(strPDF)).Select
    Selection.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=ClientFilename, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    
    
End Sub 

RE: Error 9 - Subscript out of range selecting sheets from UserForm

Seems to me that there are situations where strPDF will have a hanging China, which is likely to host things.

RE: Error 9 - Subscript out of range selecting sheets from UserForm

(OP)
Hello strongm,
That could be the case. However, when I select items and include the Dep_WD_Summary option this is what I get in the immediate window:

CODE -->

? strpdf
"Non_Reg_Client", "TFSA_Client", "InvestmentAccountSummary", "Dep_WD_Summary" 

Given the above I am not sure why Sheets(Array(strPDF)).Select does not give me the same results as the following.

When I record the macro here is what I see:

CODE -->

Sheets(Array("Non_Reg_Client", "TFSA_Client", "InvestmentAccountSummary", _
        "Dep_WD_Summary")).Select
    Sheets("Non_Reg_Client").Activate
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\Desktop\ClientName.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False 

Been banging my head against the desk for some time trying to figure this one out.

RE: Error 9 - Subscript out of range selecting sheets from UserForm

Array converts list into array, as you need to pass array as argument. Sheets(Array(strPDF)) statement has single element array containing whole string (you can test Array(strPDF) with LBbound, UBound and Array(strPDF)(0) ). No such sheet, so the error.
Use Sheets(Split(strPDF,", ")) instead (or "," and join sheet names without space). Remove double quotes from the string.

combo

RE: Error 9 - Subscript out of range selecting sheets from UserForm

Seems a waste of time and effort to build a comma separated list then use split to blow it apart into array elements.

Why not just directly populate an array and pass that to sheets(myArray).select

RE: Error 9 - Subscript out of range selecting sheets from UserForm

(OP)
Hello mintjulep,
I agree . . but, I am working within my current skillset and "directly populate an array" is what is eluding me.
I have done a variation of the above code with:

CODE -->

dim strPDF(9) as Variant

If me.chkNonRegClient = True Then
strPDF(0) = "Non_Reg_Client"
End if If me.chkNonRegSpouse = True Then
strPDF(1) = "Non_Reg_Spouse"
End if
continuing until:

CODE -->

Sheets(Array(strPDF()).Select 
I still get the same Error 9 - Subscript out of Range error.

I am stumped at how to directly populate the array based on checkbox values from a userform. Any assistance would be greatly appreciated.

RE: Error 9 - Subscript out of range selecting sheets from UserForm

(OP)
Hello combo,
I will have to try Sheets(Split(strPDF,",")) when I get back to my production computer to test this out. I will respond with the results later.

RE: Error 9 - Subscript out of range selecting sheets from UserForm

As a starting point.

CODE --> VBA

Option Explicit
Option Base 0

Public Sub CommandButton1_Click()

    Dim Count As Integer
    
    Dim ChkboxArray() As Variant
    ChkboxArray = Array("Checkbox1", "Checkbox2", "Checkbox3")
    
    Dim SheetsNameArray() As Variant
    SheetsNameArray = Array("Sheet1", "Sheet2", "Sheet3")
    
    Dim SelectionCollection As New Collection
     
    For Count = LBound(ChkboxArray) To UBound(ChkboxArray)
        If Me.Controls(ChkboxArray(Count)) Then SelectionCollection.Add (SheetsNameArray(Count))
    Next Count
  
    Sheets(CollectionToArray(SelectionCollection)).Select

End Sub

Function CollectionToArray(C As Collection) As String()

    Dim A() As String
    ReDim Preserve A(C.Count - 1)

    Dim i As Integer

    For i = 1 To C.Count

        A(i - 1) = C.Item(i)

    Next

    CollectionToArray = A

End Function 

RE: Error 9 - Subscript out of range selecting sheets from UserForm

Revise strPDF structure, no double quotes inside, if space inside sheet's name then the name in single quotation marks, as in external references in formulas. If you use single "," in Split function, remove spaces after commas in strPDF.

combo

RE: Error 9 - Subscript out of range selecting sheets from UserForm

Check the spelling of the sheet names exactly matches what you are putting in the array

Basically, if any of the array items doesn't match exactly an existing sheet name, then you'll get the subscript error. Which is (one of) the reasons why your most recent example code does notgeneraly work, since you dim it with 10 elements, but don't necessarily populate all those elements. We can fix this with only a minor refactoring of your code, something like:

CODE

    Dim strPDF() As String
    ReDim Preserve strPDF(0) As String

    If Me.chkNonRegClient = True Then
        strPDF(UBound(strPDF)) = "Non_Reg_Client"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
    
    If Me.chkNonRegSpouse = True Then
        strPDF(UBound(strPDF)) = "Non_Reg_Spouse"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
    '...
    '...
    '...
    ReDim Preserve strPDF(UBound(strPDF) - 1)
    Sheets(strPDF).Select 'NOT Sheets(Array(strPDF()).Select 

This will achieve much the same affect as mintjulip's collection method - an array that ONLY has the correct number of elements, each with a sheetname in it.

Another alternative would be to group all the CheckBoxes into a Frame, and set each of their tags to the relevant sheetname, and then do something like the following:

CODE

Private Sub CommandButton1_Click()
    Dim myctrl As Control
    Dim strPDF() As String
    ReDim strPDF(9) As String
    Dim itemcount As Long
    
    For Each myctrl In Frame1.Controls
        If TypeName(myctrl) = "CheckBox" Then
            If myctrl.Value = True Then
                strPDF(itemcount) = myctrl.Tag
            End If
            itemcount = itemcount + 1
        End If
       
    Next
    
    ReDim Preserve strPDF(itemcount - 1) As String
    Sheets(strPDF).Select
End Sub 

RE: Error 9 - Subscript out of range selecting sheets from UserForm

Store sheet names and control names in a hidden sheet to remove hard-coded configuration information.
Better align control names and sheet names to condense the table to one column.
Generate checkboxes on the UserForm on the fly.

RE: Error 9 - Subscript out of range selecting sheets from UserForm

Quote (mintjulep)

Generate checkboxes on the UserForm on the fly.
Actually listbox with ListStyle set to 1 (fmListStyleOption) and MultiSelect set to 1 (fmMultiSelectMulti) can be used instead, not nice list, but scrollable and without runtime controls. Second hidden column or separate array can be used to store sheet names.

Before printing to pdf I would check if the user checked anything to print to, basing on the initial post (can be easily replaced by array and loop, as mintjulep and strongm suggested):

CODE -->

Dim SelReplace As Boolean
bSelReplace = True ' no prior selection, start new
If Me.chkNonRegClient = True Then Sheets("Non_Reg_Client").Select bSelReplace: bSelReplace = False
If Me.chkNonRegSpouse = True Then Sheets("Non_Reg_Spouse").Select bSelReplace: bSelReplace = False
' etc.
If bSelReplace Then
    MsgBox "Nothing to print selected"
Else
    SelectedSheets.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=ClientFilename, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
End If 

combo

RE: Error 9 - Subscript out of range selecting sheets from UserForm

(OP)
Hello All,
I have reworked my code as per strongm's suggestion:

CODE -->

'Non Reg Client
    If Me.chkNonRegClient = True Then
        strPDF(UBound(strPDF)) = "Non_Reg_Client"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
    'Non Reg Spouse
    If Me.chkNonRegSpouse = True Then
        strPDF(UBound(strPDF)) = "Non_Reg_Spouse"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
' ---
' ---
    Sheets(strPDF).Select
    Sheets(strPDF(LBound(strPDF))).Activate '<--- I had to add this as the pdf output was only the strPDF array number, not the values on the sheet
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=ClientFilename, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False 

combo: your suggestion of Sheets(Split(strPDF)).Select should have worked (I did additional research on this methodology) but I was still getting "Error 9"
mintjulep: I need to spend more time developing my working knowledge of Array's to be able to fully understand and appreciate your suggestions.

Thank you to everyone who took the time to offer their knowledge and expertise.

RE: Error 9 - Subscript out of range selecting sheets from UserForm

You should have proper structure of strPDF string. So you should build it with (assuming it will be splitted with single comma, without space: ","):
strPDF = strPDF & iif(Len(strPDF)=0,"",",") &"Non_Reg_Client"
I.e. commas only between sheet names, no space after comma, no quotation marks around sheet names.

From your initial code I expected that you need to export in general multiple sheets. In the accepted code you activate first worksheet of the selection and export it. If the selection contains multiple sheets and you plan to process them all, then instead of Sheets(strPDF(LBound(strPDF))).Activate and next code line, use the code between Else and End If in my example

combo

RE: Error 9 - Subscript out of range selecting sheets from UserForm

(OP)
Hello combo;
Your comment about checking to see if anything was selected before exporting makes sense. So, I followed your example and wrote the following:

CODE -->

Sub ExportToPDF()
    'On Error Resume Next

    Dim strPDF As String
    Dim bSelReplace As Boolean
    Dim ClientName As String
    Dim ClientFilename As Variant

    bSelReplace = True
    ClientName = Range("Client_First") & Range("Client_LasT")

    If Me.chkNonRegClient = True Then Sheets("Non_Reg_Client").Select bSelReplace: bSelReplace = False
    If Me.chkNonRegSpouse = True Then Sheets("Non_Reg_Spouse").Select bSelReplace: bSelReplace = False
    If Me.chkTFSAClient = True Then Sheets("TFSA_Client").Select bSelReplace: bSelReplace = False
    If Me.chkTFSASpouse = True Then Sheets("TFSA_Spouse").Select bSelReplace: bSelReplace = False
    If Me.chkRRSPRRIFClient = True Then Sheets("RRSP_RRIF_Client").Select bSelReplace: bSelReplace = False
    If Me.chkRRSPRRIFSpouse = True Then Sheets("RRSP_RRIF_Spouse").Select bSelReplace: bSelReplace = False
    If Me.chkLockedLIFClient = True Then Sheets("Locked_LIF_Client").Select bSelReplace: bSelReplace = False
    If Me.chkLokcedLIFSpouse = True Then Sheets("Locked_LIF_Client").Select bSelReplace: bSelReplace = False
    If Me.chkIAS = True Then Sheets("InvestmentAccountSummary").Select bSelReplace: bSelReplace = False
    If Me.chkDWDSummary = True Then Sheets("Dep_WD_Summary").Select bSelReplace: bSelReplace = False
 
    'Save selected worksheets as a PDF
    ClientFilename = Application.GetSaveAsFilename( _
    InitialFileName:=ClientName, _
    filefilter:="PDF, *.pdf", _
    Title:="Save As PDF")
    
    If bSelReplace Then
        MsgBox "Nothing to print selected"
    Else
        SelectedSheets.ExportAsFixedFormat _ '<----- Broke here with Error 424: Object Required
            Type:=xlTypePDF, _
            Filename:=ClientFilename, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
    End If
    
    Unload Me
    
End Sub 

I was able to see that the sheets that I had selected from the form were in fact selected, so I am not sure why the error.

RE: Error 9 - Subscript out of range selecting sheets from UserForm

(OP)
Hello combo;
Using the Split() and iif(), I wrote the following:

CODE -->

Sub ExportToPDFString()
    Dim strPDF As String
    Dim ClientName As String
    Dim ClientFilename As Variant

    ClientName = Range("Client_First") & Range("Client_LasT")
    
    'Non Reg Client
    If Me.chkNonRegClient = True Then
        strPDF = strPDF & IIf(Len(strPDF) = 0, "", ",") & "Non_Reg_Client"
    End If
    'Non Reg Spouse
    If Me.chkNonRegSpouse = True Then
        strPDF = strPDF & IIf(Len(strPDF) = 0, "", ",") & "Non_Reg_Spouse"
    End If
    'TFSA Client
    If Me.chkTFSAClient = True Then
        strPDF = strPDF & IIf(Len(strPDF) = 0, "", ",") & "TFSA_Client"
    End If
    'TFSA Spouse
    If Me.chkTFSASpouse = True Then
        strPDF = strPDF & IIf(Len(strPDF) = 0, "", ",") & "TFSA_Spouse"
    End If
    'RRSP/RRIF Client
    If Me.chkRRSPRRIFClient = True Then
        strPDF = strPDF & IIf(Len(strPDF) = 0, "", ",") & "RRSP_RRIF_Client"
    End If
    'RRSP/RRIF Spouse
    If Me.chkRRSPRRIFSpouse = True Then
        strPDF = strPDF & IIf(Len(strPDF) = 0, "", ",") & "RRSP_RRIF_Spouse"
    End If
    'Locked/LIF Client
    If Me.chkLockedLIFClient = True Then
        strPDF = strPDF & IIf(Len(strPDF) = 0, "", ",") & "Locked_LIF_Client"
    End If
    'Locked/LIF Spouse
    If Me.chkLokcedLIFSpouse = True Then
        strPDF = strPDF & IIf(Len(strPDF) = 0, "", ",") & "Locked_LIF_Spouse"
    End If
    'Investment Account Summary
    If Me.chkIAS = True Then
        strPDF = strPDF & IIf(Len(strPDF) = 0, "", ",") & "InvestmentAccountSummary"
    End If
    'Deposit/Withdrawal Summary
    If Me.chkDWDSummary = True Then
        strPDF = strPDF & IIf(Len(strPDF) = 0, "", ",") & "Dep_WD_Summary"
    End If
    
    
    'Save selected worksheets as a PDF
    ClientFilename = Application.GetSaveAsFilename( _
    InitialFileName:=ClientName, _
    filefilter:="PDF, *.pdf", _
    Title:="Save As PDF")

    Sheets(Split(strPDF, ",")).Select
    Selection.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=ClientFilename, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    
    Unload Me
    
End Sub 

It did in fact export to a PDF, but only the array numbers were visible on the pdf. The information on the sheets was not there. I can see the sheets that I selected from the form were in fact selected.

RE: Error 9 - Subscript out of range selecting sheets from UserForm

It should work if you have sheets selected and call ExportAsFixedFormat for any single worksheet.
In the first example, for instance:
ActiveWindow.SelectedSheets(1).ExportAsFixedFormat _
In the second one:
Sheets(Split(strPDF, ",")).Select
Sheets(Split(strPDF, ","))(1).ExportAsFixedFormat _

where Sheets(Split(strPDF, ","))(1) is just to have quick reference to a worksheet, you can easily find a better one.

combo

RE: Error 9 - Subscript out of range selecting sheets from UserForm

(OP)
Hello All,
I truly appreciate all the help and feedback to my question. I have taken combo's suggestion and wrote the following:

CODE -->

Sub ExportToPDFBoolean()
'December 2019: I like this one as it is clean and short, and I can explain how it works :)
    On Error Resume Next

    Dim strPDF As String
    Dim bSelReplace As Boolean
    Dim ClientName As String
    Dim ClientFilename As Variant

    bSelReplace = True
    ClientName = Range("Client_First") & Range("Client_LasT")
    
    'Checks to see which checkboxes have been selected (True)
    If Me.chkNonRegClient = True Then Sheets("Non_Reg_Client").Select bSelReplace: bSelReplace = False
    If Me.chkNonRegSpouse = True Then Sheets("Non_Reg_Spouse").Select bSelReplace: bSelReplace = False
    If Me.chkTFSAClient = True Then Sheets("TFSA_Client").Select bSelReplace: bSelReplace = False
    If Me.chkTFSASpouse = True Then Sheets("TFSA_Spouse").Select bSelReplace: bSelReplace = False
    If Me.chkRRSPRRIFClient = True Then Sheets("RRSP_RRIF_Client").Select bSelReplace: bSelReplace = False
    If Me.chkRRSPRRIFSpouse = True Then Sheets("RRSP_RRIF_Spouse").Select bSelReplace: bSelReplace = False
    If Me.chkLockedLIFClient = True Then Sheets("Locked_LIF_Client").Select bSelReplace: bSelReplace = False
    If Me.chkLokcedLIFSpouse = True Then Sheets("Locked_LIF_Client").Select bSelReplace: bSelReplace = False
    If Me.chkIAS = True Then Sheets("InvestmentAccountSummary").Select bSelReplace: bSelReplace = False
    If Me.chkDWDSummary = True Then Sheets("Dep_WD_Summary").Select bSelReplace: bSelReplace = False
 
    
    If bSelReplace Then
        MsgBox "Need to select one item to export to PDF", , "Nothing selected"
        Exit Sub
    Else
        'Save selected worksheets as a PDF
        ClientFilename = Application.GetSaveAsFilename( _
        InitialFileName:=ClientName, _
        filefilter:="PDF, *.pdf", _
        Title:="Save As PDF")
        
        'Export to PDF
        ActiveWindow.SelectedSheets(1).ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=ClientFilename, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
    End If
    
    Unload Me
    
End Sub 

Thank you. It works and it has the added benefit of checking to see if the user has actually selected something to export to PDF.

RE: Error 9 - Subscript out of range selecting sheets from UserForm

(OP)
Hello All,
In the interest of helping other people who are searching for a similar solution. Here is the code to ExportToPDF using an Array.

CODE -->

Sub ExportToPDFArray()
    'December 2019: Uses an Array to collect which sheets have been selected to be exported to PDF
    Dim strPDF() As String
    Dim ClientName As String
    Dim ClientFilename As Variant
    
    'Resizes array
    ReDim Preserve strPDF(0) As String

    ClientName = Range("Client_First") & Range("Client_Last")
    
    'This resized the array to include the new value added
    'ReDim Preserve strPDF(UBound(strPDF) + 1)

    'Non Reg Client
    If Me.chkNonRegClient = True Then
        strPDF(UBound(strPDF)) = "Non_Reg_Client"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
    'Non Reg Spouse
    If Me.chkNonRegSpouse = True Then
        strPDF(UBound(strPDF)) = "Non_Reg_Spouse"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
    'TFSA Client
    If Me.chkTFSAClient = True Then
        strPDF(UBound(strPDF)) = "TFSA_Client"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
    'TFSA Spouse
    If Me.chkTFSASpouse = True Then
         strPDF(UBound(strPDF)) = "TFSA_Spouse"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
    'RRSP/RRIF Client
    If Me.chkRRSPRRIFClient = True Then
        strPDF(UBound(strPDF)) = "RRSP_RRIF_Client"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
    'RRSP/RRIF Spouse
    If Me.chkRRSPRRIFSpouse = True Then
        strPDF(UBound(strPDF)) = "RRSP_RRIF_Spouse"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
    'Locked/LIF Client
    If Me.chkLockedLIFClient = True Then
        strPDF(UBound(strPDF)) = "Locked_LIF_Client"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
    'Locked/LIF Spouse
    If Me.chkLokcedLIFSpouse = True Then
        strPDF(UBound(strPDF)) = "Locked_LIF_Spouse"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
    'Investment Account Summary
    If Me.chkIAS = True Then
        strPDF(UBound(strPDF)) = "InvestmentAccountSummary"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
    'Deposit/Withdrawal Summary
    If Me.chkDWDSummary = True Then
        strPDF(UBound(strPDF)) = "Dep_WD_Summary"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If

    'Save selected worksheets as a PDF
    ClientFilename = Application.GetSaveAsFilename( _
    InitialFileName:=ClientName, _
    filefilter:="PDF, *.pdf", _
    Title:="Save As PDF")

    ReDim Preserve strPDF(UBound(strPDF) - 1)

    Sheets(strPDF).Select
    Sheets(strPDF(LBound(strPDF))).Activate
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=ClientFilename, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False


    Unload Me

End Sub 

Once again, thank you to strongm for his help with the above code.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close