Loop through drop down list and save range as PDF

Jasesair

Active Member
Joined
Apr 8, 2015
Messages
265
Office Version
  1. 2016
I have a drop down list (Data Validation List) in cell A3 of a sheet called "End of Season". When I select from the drop down list, the data in the sheet changes appropriately. I'd like some code that loops through every drop down and saves each time as a PDF. Ideally, this would save as one PDF but I can handle if each loop saves a separate PDF. The range to be saved is A1:X39 each time.

Any help to do this would be very much appreciated - it'd be a real time saver.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,049
This macro creates one PDF in the same folder as the workbook.
VBA Code:
Public Sub Create_PDF()

    Dim PDFfullName As String
    Dim PDFsheet As Worksheet
    Dim destCell As Range
    Dim dataValidationCell As Range, dataValidationListSource As Range, dvValueCell As Range
    Dim copyRange As Range
    
    With ActiveWorkbook
    
        'PDF file name
        
        PDFfullName = .Path & "\End of Season.pdf"
        
        'Add temporary sheet for PDF output
        
        Set PDFsheet = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
        Set destCell = PDFsheet.Range("A1")
    
        'Cell containing data validation in-cell dropdown
    
        Set dataValidationCell = .Worksheets("End of Season").Range("A3")
        
        'Range to be copied to temporary sheet for each dropdown value
        
        Set copyRange = .Worksheets("End of Season").Range("A1:X39")
        
    End With
         
    'Source of data validation list
    
    Set dataValidationListSource = Evaluate(dataValidationCell.Validation.Formula1)
     
    'Loop through each data validation value to update range
    
    For Each dvValueCell In dataValidationListSource
    
        dataValidationCell.Value = dvValueCell.Value
        
        'Copy range to next cell in temporary PDF sheet
                
        copyRange.Copy
        destCell.Select
        destCell.Worksheet.Paste
        destCell.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
        'Update destination cell to next row
        
        With PDFsheet
            Set destCell = .Cells(.UsedRange.Rows.Count + 1, 1)
        End With
        
    Next
        
    'Save temporary sheet as PDF then delete it
    
    With PDFsheet
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfullName, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
        Application.DisplayAlerts = False
        .Delete
        Application.DisplayAlerts = True
    End With
    
End Sub
 

Jlex

New Member
Joined
Apr 16, 2021
Messages
1
Office Version
  1. 2010
Platform
  1. Windows
Now when I run this the pdf opens as portrait, if I needed to export in Landscape how would that work. I’ve tried the basic vbas to swap to landscape but because it’s creating a temporary and deleting the print settings are working.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,049
Set the page to landscape on the sheet's Page Layout tab -> Orientation, not the print settings. Recording a macro will give you the basic code, which can be reduced to a single line inside the With PDFsheet ... End With block:
VBA Code:
    With PDFsheet
        .PageSetup.Orientation = xlLandscape
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfullName, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
        Application.DisplayAlerts = False
        .Delete
        Application.DisplayAlerts = True
    End With
 

Jasesair

Active Member
Joined
Apr 8, 2015
Messages
265
Office Version
  1. 2016

ADVERTISEMENT

I'm having trouble with this macro. It is producing one PDF but with some issues. A picture that's included in the range is coming through to PDF at a much larger size than the remainder of the cells, and values are not changing. The drop down changes but not the values that should as a result of the drop down selection. Any ideas?
 

Jasesair

Active Member
Joined
Apr 8, 2015
Messages
265
Office Version
  1. 2016
I'm having trouble with this macro. It is producing one PDF but with some issues. A picture that's included in the range is coming through to PDF at a much larger size than the remainder of the cells, and values are not changing. The drop down changes but not the values that should as a result of the drop down selection. Any ideas?
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,049

ADVERTISEMENT

My first macro copies cell formulas, and if these contain absolute cell references (e.g. $A$3), every copy of the A1:X39 range in the temporary PDF sheet will reference the $A$3 cell in the temporary sheet. That's why the dropdown values change, but not the other cell values.

Try this macro, which copies cell formats, column widths, the picture and cell values (instead of cell formulas) to the temporary PDF sheet. I've also added a commented-out line which inserts a page break between each copy of the A1:X39 range, should you need it.
VBA Code:
Public Sub Create_PDF2()

    Dim PDFfullName As String
    Dim PDFsheet As Worksheet
    Dim destCell As Range
    Dim dataValidationCell As Range, dataValidationListSource As Range, dvValueCell As Range
    Dim copyRange As Range
    
    With ActiveWorkbook
    
        'PDF file name
        
        PDFfullName = .Path & "\End of Season.pdf"
        
        'Add temporary sheet for PDF output
        
        Set PDFsheet = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
        Set destCell = PDFsheet.Range("A1")
    
        'Cell containing data validation in-cell dropdown
    
        Set dataValidationCell = .Worksheets("End of Season").Range("A3")
        
        'Range to be copied to temporary sheet for each dropdown value
        
        Set copyRange = .Worksheets("End of Season").Range("A1:X39")
        
    End With
         
    'Source of data validation list
    
    Set dataValidationListSource = Evaluate(dataValidationCell.Validation.Formula1)
     
    'Loop through each data validation value to update range
    
    For Each dvValueCell In dataValidationListSource
    
        dataValidationCell.Value = dvValueCell.Value
        
        'Copy cell formats, column widths, the picture and cell values to next cell in temporary PDF sheet
        
        copyRange.Copy
        destCell.Select
        destCell.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        destCell.Worksheet.Paste
        destCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
        'Update destination cell to next row
        
        With PDFsheet
            '.HPageBreaks.Add Before:=.Rows(.UsedRange.Rows.Count + 1)
            Set destCell = .Cells(.UsedRange.Rows.Count + 1, 1)
        End With
        
    Next
        
    'Save temporary sheet as PDF then delete it
    
    With PDFsheet
        .PageSetup.Orientation = xlPortrait 'xlLandscape
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfullName, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
        Application.DisplayAlerts = False
        .Delete
        Application.DisplayAlerts = True
    End With
    
End Sub
 

Jasesair

Active Member
Joined
Apr 8, 2015
Messages
265
Office Version
  1. 2016
That's sounding great. Running it, I've picked up a Run-Time error 1004 saying 'To do this, all the merged cells need to be the same size' which is highlighting the below line:
destCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,049
Run-Time error 1004 saying 'To do this, all the merged cells need to be the same size' which is highlighting the below line:
destCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Change xlPasteValues to xlPasteValuesAndNumberFormats.
 

Jasesair

Active Member
Joined
Apr 8, 2015
Messages
265
Office Version
  1. 2016
Many thanks - the macro is back working again. When this is copying to the temporary sheet, it must not be bringing the row widths with it, which means it won't look as it should. Is there a possible fix for this? I'm new to VBA so have been Googling but can't work this out myself unfortunately. My aim is for the copying to be the same as the original sheet with column widths, row heights, margins etc.
 

Forum statistics

Threads
1,144,369
Messages
5,723,952
Members
422,528
Latest member
IMK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top