VBA to cycle through dropdown list and export into pdf

imazhary

New Member
Joined
May 22, 2018
Messages
1
Hi all

I have created a dashboard which updates based on a dropdown list. I need a macro which will cycle through all of the options in the dropdown list and also export a pdf copy of each updated dashboard. The current excel model takes a considerable time to recalculate after each dropdown change so I need a way of automating it.

Thanks in advance for any advice!
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,320
Office Version
365
Platform
Windows
Use Something like this (assumed that dropdown values stored in a named range - if that is incorrect let me know :) )

To test
- amend every constant values to match your own and run the code (- fpath is the full path to save folder )


Code:
[COLOR=#000080]Sub Test1()[/COLOR]
Const NamedRangeName = "[COLOR=#ff0000]Choices[/COLOR]"
Const SheetName = [COLOR=#ff0000]"MyDashboardSheet[/COLOR]"
Const CellWithDropdown = "[COLOR=#ff0000]A1[/COLOR]"
Const PrintRange = "[COLOR=#ff0000]A1:E40[/COLOR]"
Const fPath = "[COLOR=#ff0000]C:\Folder\SubFolder[/COLOR]"

Dim choice As Range
With Sheets(SheetName)
    For Each choice In Range(NamedRangeName)
        .Range(CellWithDropdown) = choice
        fname = choice & Format(Date, " yymmdd") & ".pdf"
        .Range(PrintRange).ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & "\" & fname
    Next
End With
[COLOR=#000080]End Sub[/COLOR]
If, for any reason you cannot get it running, post your full code enclosed in code tags
(by clicking first on #icon - which is found above post window - and posting your code between the code tags [CODE ] code goes here [/CODE ] )
 
Last edited:

tzallas

New Member
Joined
May 18, 2019
Messages
8
Use Something like this (assumed that dropdown values stored in a named range - if that is incorrect let me know :) )

To test
- amend every constant values to match your own and run the code (- fpath is the full path to save folder )


Code:
[COLOR=#000080]Sub Test1()[/COLOR]
Const NamedRangeName = "[COLOR=#ff0000]Choices[/COLOR]"
Const SheetName = [COLOR=#ff0000]"MyDashboardSheet[/COLOR]"
Const CellWithDropdown = "[COLOR=#ff0000]A1[/COLOR]"
Const PrintRange = "[COLOR=#ff0000]A1:E40[/COLOR]"
Const fPath = "C:\Folder\SubFolder"

Dim choice As Range
With Sheets(SheetName)
    For Each choice In Range(NamedRangeName)
        .Range(CellWithDropdown) = choice
        fname = choice & Format(Date, " yymmdd") & ".pdf"
        .Range(PrintRange).ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & "\" & fname
    Next
End With
[COLOR=#000080]End Sub[/COLOR]
If, for any reason you cannot get it running, post your full code enclosed in code tags
(by clicking first on #icon - which is found above post window - and posting your code between the code tags [CODE ] code goes here [/CODE ] )

So this worked perfectly for me, plugged in my Const where needed and all is well.
I also added a dynamic Named range with an offset function, so the drop down list automatically fills up as new rows are added in my case.

My question is how can we change it so that the user is propmted for a folder destination for the PDF exports to be saved in?

i.e. User clicks macro button, file browser window pops up, "select destination folder for pdf export", browse+select, click ok, macro continues and finishes as usual.

Thank you
 

tzallas

New Member
Joined
May 18, 2019
Messages
8

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,320
Office Version
365
Platform
Windows
@tzallas
Welcome to the board
- old threads are often not monitored (and this one is very old)
- (for the future) you are more likely to get a response by starting a new thread to ask your question (with a link to an old thread if helpful)

The RED lines set the initial folder
- amend the folder path to whichever is relevant to you
- delete both if not required

Code:
Sub Test1()
Const NamedRangeName = "Choices"
Const SheetName = "MyDashboardSheet"
Const CellWithDropdown = "A1"
Const PrintRange = "A1:E40"
[COLOR=#ff0000]Const DefaultFolder = "C:\Test\PDF"[/COLOR]
Dim fPath As String, choice As Range, fName As String
[COLOR=#006400]'allow user to select folder[/COLOR]
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select destination folder for PDF export"
      [COLOR=#ff0000]  .InitialFileName = DefaultFolder[/COLOR]
        If .Show = -1 Then fPath = .SelectedItems(1)
    End With

[COLOR=#006400]'rest of code[/COLOR]
    If fPath <> "" Then
        With Sheets(SheetName)
            For Each choice In Range(NamedRangeName)
                .Range(CellWithDropdown) = choice
                fName = choice & Format(Date, " yymmdd") & ".pdf"
                .Range(PrintRange).ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & "\" & fName
            Next
        End With
    Else
        MsgBox "No folder selected"
    End If

End Sub

You may find this link helpful
https://wellsr.com/vba/2016/excel/vba-select-folder-with-msoFileDialogFolderPicker/
 
Last edited:

tzallas

New Member
Joined
May 18, 2019
Messages
8
Thank you for the reply, fair point about the old post comment. I did notice it was quite old but saw that you had last edited it in 2018, so I gave it a shot.
Will follow your advice in the future.:)

I will try your amendments this evening and let you know if all goes well

(y)
 

tzallas

New Member
Joined
May 18, 2019
Messages
8
@tzallas
Welcome to the board
- old threads are often not monitored (and this one is very old)
- (for the future) you are more likely to get a response by starting a new thread to ask your question (with a link to an old thread if helpful)

The RED lines set the initial folder
- amend the folder path to whichever is relevant to you
- delete both if not required

Code:
Sub Test1()
Const NamedRangeName = "Choices"
Const SheetName = "MyDashboardSheet"
Const CellWithDropdown = "A1"
Const PrintRange = "A1:E40"
[COLOR=#ff0000]Const DefaultFolder = "C:\Test\PDF"[/COLOR]
Dim fPath As String, choice As Range, fName As String
[COLOR=#006400]'allow user to select folder[/COLOR]
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select destination folder for PDF export"
      [COLOR=#ff0000]  .InitialFileName = DefaultFolder[/COLOR]
        If .Show = -1 Then fPath = .SelectedItems(1)
    End With

[COLOR=#006400]'rest of code[/COLOR]
    If fPath <> "" Then
        With Sheets(SheetName)
            For Each choice In Range(NamedRangeName)
                .Range(CellWithDropdown) = choice
                fName = choice & Format(Date, " yymmdd") & ".pdf"
                .Range(PrintRange).ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & "\" & fName
            Next
        End With
    Else
        MsgBox "No folder selected"
    End If

End Sub

You may find this link helpful
https://wellsr.com/vba/2016/excel/vba-select-folder-with-msoFileDialogFolderPicker/
Worked perfectly!!
I noodled around and also added a msgbox for the users so it is clear what the file browser is asking from them (since the browser title is hard to catch, this makes them press ok first befoe selecting the destination folder) and its all working like clockwork!

Thank you (y)

Now to incorporate this with only one file destination prompt for all and worksheets closing after completion, the head scratching begins.:banghead:
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,320
Office Version
365
Platform
Windows
Like I said earlier , start a new thread .... ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,050
Messages
5,484,405
Members
407,438
Latest member
DKrakken

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top