Data List Change + PDF Export on button click

[StevenE06]

Im really new to macros and trying to learn by looking at examples but have hit a road block here.

The excel file I am working on has 3 tabs/sheets. Sorted Data, Quarterly Data, and Data Validation.
The sorted data has a Unit Number drop down list(Data Validation list) from the DV sheet. The selection can and will change quarterly.
The list is reference is

='Data Validation'!$A:$A

because the list may grow or shrink each quarter (Company is a selectable option so it gives a total for all unit numbers)

I have a button next to the unit selection that will export to PDF and name the PDF based on the unit number selected.
I am trying to get the button to cycle through all the options in the list (Data Validation list) and export each one to PDF.
The cell for the drop down list is a merged cell on C6/D6

This is the PDF export button code I currently have

Sub CreatePDF()

Dim ID As String

ID = Range("C6").Text

    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    FileName:="C:\Documents\" + ID + ".pdf", _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

End Sub

Anyone able to help me out here?

Thank you in advance!

 

[John_w]

Try this macro:

Public Sub Create_PDF_For_Each_Company()

    Dim DVrange As Range
    Dim companyValues As Variant, company As Variant
    
    With ActiveSheet
        Set DVrange = Range(.Range("C6").Validation.Formula1)
        With DVrange.Worksheet
            companyValues = .Range(DVrange(2), .Cells(.Rows.Count, DVrange.Column).End(xlUp)).Value
        End With
        For Each company In companyValues
            .Range("C6").Value = company
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Documents\" & company & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Next
    End With

End Sub

 

[StevenE06]

Hey

So it cycles through the "options" but its not pulling data when it does.

Here is what it shows when I manually drop down and select a Unit Number

However when the macro runs and it does the export on them it does them all like this

 

[John_w]

So it cycles through the "options" but its not pulling data when it does.

Without your workbook or a more detailed explanation of the worksheet I can't explain why it's not pulling the data.

Perhaps upload a copy of your workbook or post the worksheets using the forum's XL2BB tool.

 

[StevenE06]

Without your workbook or a more detailed explanation of the worksheet I can't explain why it's not pulling the data.

Perhaps upload a copy of your workbook or post the worksheets using the forum's XL2BB tool.

Hey John

I will send you a message directly as I cannot download the XL2BB tool on this computer.