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
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