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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,388
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,388
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,388
Office Version
365
Platform
Windows
Like I said earlier , start a new thread .... ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,891
Messages
5,489,556
Members
407,700
Latest member
SimpleJuan

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top