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)
- The folder path generated by the dialog box FileDialog(msoFileDialogFolderPicker) only works for the first "For Each" loop.
- 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.
I have tried Calling the Sub FilterRows_SiteSpecific from within the For Each loop but it makes no difference to the output, the AutoFilter does not work.
Any help gratefully received.
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
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
ActiveWorkbook.Close
Next rngListSelection
Application.ScreenUpdating = True
' *********************
End If
End Sub
Sub FilterRows_SiteSpecific()
' VLookupForAutoFilter Macro
' Set VLookup calculation output as Integer variable
Dim intColumn As Integer
' Calculation must start on woksheet "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
