Excel VBA Create user generated Folder path to use in For Each loop

[chriswheel]

I have looked through other answers but it appears that my problems are addressed only partially, as the issues are across multiple issues.

BACKGROUND
Every month, a workbook is opened with links to external data and reports are run for each site.
The file updates as each site is accessed from a drop down list.
After the report is generated, each site has a filter applied, based on their particular contractual requirements: the filters are is separate columns, with cells either "" or "y".

ACTIONS TO DATE
I have built some code to loop through the drop down list (in cell "H1") and save the resulting worksheets to a new, separate location, under the name of the Site location in cell "H1".
I have code to filter, based on the Site location in cell "H1".
I have also written code to ask the user to pick the Save To folder location.

PROBLEM(S)
1. The folder path generated by the dialog box FileDialog(msoFileDialogFolderPicker) only works for the first "For Each" loop.
2. I do not know where to put the AutoFilter code into the "For Each" loop.

I think that #1 is due to the Folder path generated being a variable and I need to be able to declare it as a constant....?

For the AutoFilter, again, I think that the problem is accessing the name from the DDL whilst in the For Each loop: no idea how to solve that one.

I might be using the wrong approach to achieving the outcome I want or it may just be a small tweak that is needed.

The following code works, as in the file is saved to the selected Folder, for the first item but then attempts to save every subsequent file as "FALSE" but no filter is applied.



*************************************************


Sub SelectFolderANDLoop()


Application.ScreenUpdating = False


Dim sFolder As String
' Open the select folder prompt
With Application.FileDialog(msoFileDialogFolderPicker)
'This sets the starting point for Folder selection as "T:\Business Information" etc
' Change path as necessary
.InitialFileName = "T:\Business Information\Data Submissions\Contract\DASHBOARDS"


If .Show = -1 Then ' if OK is pressed
sFolder = .SelectedItems(1)
End If
End With

If sFolder <> "" Then ' if a file was chosen
' *********************

Dim rngListSelection As Range

Dim strListSelection As String
Dim intColumn As Integer



'Looks up each site from drop down lilst in cell H1

For Each rngListSelection In Range(Range("H1").Validation.Formula1)
Range("H1").Value = rngListSelection
' Copy tabs "Site Name" and "Data"
Worksheets(Array("Site Name", "Data")).Copy
' Clears the entry for the site name
Range("H1").Validation.Delete
' Copies the 2 worksheets to a new workbook
ActiveWorkbook.SaveAs sFolder & "" rngListSelection.Value & ".xlsx", xlOpenXMLWorkbook

' Call FilterRows_SiteSpecific

ActiveWorkbook.Close
Next rngListSelection


Application.ScreenUpdating = True
' *********************
End If
End Sub


Sub Clear_All_Filters_Range()


'To Clear All Fitlers use the ShowAllData method for
'the sheet. Add error handling to bypass error if
'no filters are applied. Does not work for Tables.
On Error Resume Next
Sheet1.ShowAllData
On Error GoTo 0

End Sub

If I declare the folder path as a constant, ALL Site files are correctly generated and saved by Site name.

e.g.

Const strSAVE_PATH As String = "T:\Business Information\Data Submissions\Contract\DASHBOARDS\Reporting Changes - April 2019\Exception by Site"



< and then.....>
For Each rngListSelection In Range(Range("H1").Validation.Formula1)
Range("H1").Value = rngListSelection
Worksheets(Array("Site Name", "Data")).Copy
Range("H1").Validation.Delete
' Copies the 2 worksheets to a new workbook
ActiveWorkbook.SaveAs strSAVE_PATH & _
rngListSelection.Value & ".xlsx", xlOpenXMLWorkbook

The code for the Autofilter is as follows:-

Sub FilterRows_SiteSpecific()


' VLookupForAutoFilter Macro
' Set VLookup calculation output as Integer variable


Dim intColumn As Integer


' Calculation must start on worksheet "Site Name" and looks up Site name in cell "H1"


On Error Resume Next
intColumn = Application.WorksheetFunction.VLookup(ActiveSheet.Range("H1"), _
Worksheets("Data").Range("B125:E145"), 4, False)


On Error GoTo 0
' If the site name is not found in the VLookup table, create error message


Debug.Print intColumn

If intColumn = 0 Then
MsgBox "The unit name is mistyped as a match was not found"
Else
End If


' The above was to create a variable to provide the column number_
' to use in the Autofilter function below


With Worksheets("Site Name").Range("A2:r149")
' Filters to exclude "Info Only" in "Target" column
.AutoFilter field:=11, Criteria1:="<>Info Only"
' Filter to include "y" in column from VLOOKUP
.AutoFilter field:=intColumn, Criteria1:="y"
With Worksheets("Site Name").Range("A8:r149")
' Filters to exclude "n/a" in "Actual" column
.AutoFilter field:=10, Criteria1:="<>n/a"




End With
End With
End Sub

*************************
Any help MUCH appreciated