VBA help Looping thru Dropdowns & Saving to Fewer PDFs

[ExcelAtEverything]

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