VBA help Looping thru Dropdowns & Saving to Fewer PDFs

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
351
Office Version
  1. 2019
Platform
  1. Windows
Hi there! I was hoping someone could help me add 3 more things into my code.
VBA is not a strong suit for me, but what it does is this:
In the company, there are 4 stores total. Each store has 12 categories ( same 12 categories in each store, & they never change).
This report is located on a sheet named "CPR" which shows detailed inventory data for one single store, & one single category at a time.
My current code loops thru each primary category in a dropdown, and saves each to PDF under a unique name. There's also a line which autofits column width again before saving each time.
The category is selectable from a dropdown located in B3.
The store is selectable from a dropdown in cell B18.

3 things I'm asking for help with:
  1. Is there a way to make these save down to only 4 PDFs instead of 48 (1 PDF per store containing all 12 categories)? Not sure if this is possible given the use of the dropdowns to switch between report options, thereby causing the pdfs to be created at separate times (unless I'm overthinking it).
  2. The current code loops thru & saves each category then stops, & then I need to manually use the dropdown in B18 to select a different store & repeat the process for each store. I'd like that to be automated as well. (Starts at first store in dropdown, then prints all 12 categories, then goes to next store, etc.)
  3. I have a named range called ReportingDate. Is there a way to use that to replace the end of the hard-coded date at end of my file name? I feel like I'm doing something wrong as I can't get it work, so I just typed in the date for now
Any help greatly appreciated!


Sub LoopThroughDataValidationList()

Dim rng As Range
Dim dataValidationArray As Variant
Dim i As Integer
Dim rows As Integer


'Set the cell which contains the Data Validation list
Set rng = Sheets("CPR").Range("B3")

'If Data Validation list is not a range, ignore errors
On Error Resume Next


'Create an array from the Data Validation formula, without creating
'a multi-dimensional array from the range
rows = Range(Replace(rng.Validation.Formula1, "=", "")).rows.Count
ReDim dataValidationArray(1 To rows)

For i = 1 To rows
dataValidationArray(i) = _
Range(Replace(rng.Validation.Formula1, "=", "")).Cells(i, 1)
Next i


'If not a range, then try splitting a string
If Err.Number <> 0 Then
Err.Clear
dataValidationArray = Split(rng.Validation.Formula1, ",")
End If


'Some other error has occured so exit sub
If Err.Number <> 0 Then Exit Sub

'Reinstate error checking
On Error GoTo 0


'Loop through all the values in the Data Validation Array
For i = LBound(dataValidationArray) To UBound(dataValidationArray)

Worksheets("CPR").Columns("K:AF").AutoFit

'Create and assign variables
Dim saveLocation As String
saveLocation = "C:\Users\myname\OneDrive\Documents\CPR_" & Range("LocName").Value & "_" & Range("PCatg").Value & "__2-16-2022.PDF"

'Save Active Sheet(s) as PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation

'Change the value in the data validation cell
rng.Value = dataValidationArray(i)

'Force the sheet to recalculate
Application.Calculate

Next i

End Sub
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Forum statistics

Threads
1,176,079
Messages
5,901,274
Members
434,884
Latest member
ares0027

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