Macro exporting pdf's based on values in a validation list

Miketurney

New Member
Joined
Jan 31, 2014
Messages
4
I have the following macro that exports pdf's for every value in a validation list. Currently it's setup where I have included each value in the macro. This is fine with just a few values but I am about to add 900.

My question is, is there a way to write the macro so it exports a pdf for every value within a dropdown list until it hits a null value instead of what I have below so i don't have to repeat everything for every value in the list?

Here is what i currently have.
Sub Export_MarketSpecific()
'
' Export_MarketSpecific Macro
'
'
Sheets("Home Page").Select
Sheets("MOA-Page 1").Visible = True
Sheets("MOA-Page 1").Select
Sheets("MOA-Page 2").Visible = True
Sheets("MOA-Page 1").Select
Range("D2").Value = Range(Range("D2").Validation.Formula1)(2).Value
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"W:\LOCATION\NAME("D2") " & Format(Date, "mm-dd-yyyy") & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

Range("D2").Value = Range(Range("D2").Validation.Formula1)(3).Value
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"W:\LOCATION\NAME("D2") " & Format(Date, "mm-dd-yyyy") & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

Range("D2").Value = Range(Range("D2").Validation.Formula1)(4).Value
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"W:\LOCATION\NAME("D2") " & Format(Date, "mm-dd-yyyy") & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

Range("D2").Value = Range(Range("D2").Validation.Formula1)(5).Value
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"W:\LOCATION\NAME("D2") " & Format(Date, "mm-dd-yyyy") & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

End Sub
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Maybe...

Code:
Option Explicit

Sub Export_MarketSpecific()

    Dim sDataValidationRange      As String
    Dim rDataValidation           As Range
    Dim rCell                     As Range
    
    sDataValidationRange = Range("D2").Validation.Formula1
    
    Set rDataValidation = Range(sDataValidationRange)
    
    For Each rCell In rDataValidation
        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:="W:\LOCATION\NAME(" & _
            rCell.Value & ") " & Format(Date, "mm-dd-yyyy") & ".pdf", Quality:=xlQualityStandard, _
            IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next rCell
    
End Sub
 

Miketurney

New Member
Joined
Jan 31, 2014
Messages
4
Thank you for the help. It does go through the validation list and save an export but it does not change the information within the excel sheet for the respective selection from the validation list. There are calculated cells, so I need the sheet to refresh based on what is selected from the validation list and then export that file.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Sorry, try the following instead...

Code:
Option Explicit

Sub Export_MarketSpecific()

    Dim sDataValidationRange      As String
    Dim rDataValidation           As Range
    Dim rCell                     As Range
    
    sDataValidationRange = Range("D2").Validation.Formula1
    
    Set rDataValidation = Range(sDataValidationRange)
    
    For Each rCell In rDataValidation
       [COLOR=#ff0000]Range("D2").Value = rCell.Value
[/COLOR]       ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:="W:\LOCATION\NAME(" & _
            rCell.Value & ") " & Format(Date, "mm-dd-yyyy") & ".pdf", Quality:=xlQualityStandard, _
            IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next rCell
    
End Sub
 

Miketurney

New Member
Joined
Jan 31, 2014
Messages
4

ADVERTISEMENT

Thanks, that is working now for the values in the list but once it reaches a null value I receive the: Run-Time '5': Invalid procedure call or argument error.

Any ideas to fix that?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
In that case, try...

Code:
Option Explicit

Sub Export_MarketSpecific()

    Dim sDataValidationRange      As String
    Dim rDataValidation           As Range
    Dim rCell                     As Range
    
    sDataValidationRange = Range("D2").Validation.Formula1
    
    Set rDataValidation = Range(sDataValidationRange)
    
    For Each rCell In rDataValidation
        If Len(rCell.Value) > 0 Then
            Range("D2").Value = rCell.Value
            ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:="W:\LOCATION\NAME(" & _
                 rCell.Value & ") " & Format(Date, "mm-dd-yyyy") & ".pdf", Quality:=xlQualityStandard, _
                 IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End If
    Next rCell
    
End Sub

Also, consider using a dynamic named range to defined your list. This way the range would automatically adjust as data is added/removed. So there would be no need to have empty cells within your list. To create a dynamic named range, have a look here...

XL-CENTRAL.COM : Excel : Create a Dynamic Named Range
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,925
Members
414,416
Latest member
Nobu

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