Exporting many PDF variations depending on a cell value

[nlarski]

I work for a shoe company and I made a large workbook with many pictures and formulas in it. The purpose of this file is to be able to input a store number and see a picture of every shoe that specific store has/will be getting as well as descriptive information for each shoe. The problem is that the file is very clunky and doing anything within it takes a LONG time. Because of this, I decided to go store by store and export the workbook as a PDF so any store's information can be easily opened and viewed without bogging down the computer. The issue is that converting a couple hundred variations of this file into PDF's is taking up all my time (I cant do anything else in excel while this process is taking place).

What I would like to know is if there is any way to have a list of store numbers, have excel go one by one, calculate each store's information, and save each variation as a PDF with a variation of a predetermined name.

Any ideas/help would be appreciated!

Thanks!

 

[John_w]

Yes, this is certainly possible. Have a look at the code at Creating a macro to export PDFs in a dropdown list, which loops through a list of in-cell dropdown data validation values to generate and create different PDF files depending on the cell value.

You don't need to use a data validation list - the VBA code could loop through a list of store numbers contained in a range of cells and write each store number to the cell which causes the formulas, etc. to update the sheet, and generate the PDF files from that.

 

[nlarski]

So this pretty much looks exactly like what Im looking for. Quick question though I have modified the VBA to reflect the Data validation cell as well as the file path that I want, but I keep getting a subscript out of range error. Does this have to do with the data validation list source? I'm not sure if I need to put the cell range in there or if it is automatically getting the range from the data validation cell. Here's how I currently have the VBA:

Public Sub Create_PDFs()


Dim dataValidationCell As Range, dataValidationListSource As Range, dvValueCell As Range
Dim dropdownValue As String
Dim PDFfile As String, i As Long
Dim destinationFolder As String

destinationFolder = "C:\Users\Me\Desktop\FPW"
'destinationFolder = "C:\path\to\folder" 'Or specific folder

If Right(destinationFolder, 1) <> "" Then destinationFolder = destinationFolder & ""

'Cell containing data validation in-cell dropdown

Set dataValidationCell = Worksheets("Picture Lookup").Range("B4")

'Source of data validation list

Set dataValidationListSource = Evaluate(dataValidationCell.Validation.Formula1)

'Create PDF for each data validation value

For Each dvValueCell In dataValidationListSource
dataValidationCell.Value = dvValueCell.Value
With dataValidationCell.Worksheet
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=destinationFolder & dvValueCell.Value & ".PDF", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
Next

End Sub




Thank you!!

 

[John_w]

Which line causes the error? Click Debug on the error message and the line is highlighted in yellow.

The code automatically gets the data validation list source range from the data validation cell.

Make sure you have created the Data Validation correctly: in the dialogue, 'Allow' should be 'List' and 'Source' should be the range of cells containing the store numbers, which can be on a different sheet, and 'in-cell dropdown' should be ticked.

Also, please use CODE tags when posting VBA code to preserve the indentation and make it easier to read: click the # icon in the message editor to insert the tags.

 

[John_w]

I have changed the code so that it doesn't use a data validation cell. Instead, it loops through a range of store numbers, puts each store number in a cell which causes the sheet data to update and creates a PDF file from the current sheet data.

The code is based on the user's requirements in the thread I linked to, so it uses those sheet names. The store numbers are in sheet "Calcs" cells G2:G5, and "Statement" E2 is the cell value which the code changes to cause the "Statement" sheet data to update and the PDF is created from this sheet.

Public Sub Create_PDFs2()

    Dim PDFfile As String
    Dim destinationFolder As String
    Dim storeNumberCells As Range, storeNumberCell As Range
    Dim storeUpdateCell As Range
    
    Set storeNumberCells = Worksheets("Calcs").Range("G2:G5")
    
    Set storeUpdateCell = Worksheets("Statement").Range("E2")
    
    destinationFolder = ThisWorkbook.Path     'Same folder as workbook containing this macro
    'destinationFolder = "C:\path\to\folder\"  'Or specific folder
    
    If Right(destinationFolder, 1) <> "\" Then destinationFolder = destinationFolder & "\"
         
    'Create PDF for each store number
    
    For Each storeNumberCell In storeNumberCells
        storeUpdateCell.Value = storeNumberCell.Value
        With storeUpdateCell.Worksheet
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=destinationFolder & "Store Number " & storeUpdateCell.Value & ".PDF", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End With
    Next
        
End Sub

 

[nlarski]

This is awesome, worked perfectly. Thank you so much man!