Selected Answer
Squishy
Please try this but note it might not be perfect yet...
1) In the attached revised file, I've defined a collection Repfiles which allows you to use the same set of files for a few searches or erase that and open a new set. The macros are modified so the search isn't done by opening each file rather it looks at the open files (or at least those listed in RepFiles (which are hidden on opening).
2) This version saves the results to a sheet named like the searchValue (having first deleted any sheets which is that already)
To do this, I've declared RepFiles at a Module level (so it's there after an individual macro has run:
Public RepFiles As Collection
and moved the process of picking files to another sub (where the section in bold builds RepFiles):
Sub FilestoRepFiles()
Dim filePicker As FileDialog
create collection to hold file names
Set RepFiles = New Collection
Set filePicker = Application.FileDialog(msoFileDialogFilePicker)
With filePicker
.Filters.Clear
.Filters.Add "Excel Files", "*.xl*"
.AllowMultiSelect = True
End With
filePickerButtonClicked = filePicker.Show
If filePickerButtonClicked = -1 Then
For Each fileselected In filePicker.SelectedItems
RepFiles.Add fileselected
Next fileselected
For n = 1 To RepFiles.Count
' open file and hide from view
Workbooks.Open (RepFiles(n))
Windows(Dir(RepFiles(n))).Visible = False
Next n
End If
End Sub
This could be slow if your have very large files.
Then the Search macro becomes this (changes in bold):
Sub Search_Separate_Workbooks()
Dim wbMaster As Workbook
Dim wbSlave As Workbook
Dim ws As Worksheet, wbReport As Worksheet, ReportNm As String
On Error Resume Next
ReportNm = Worksheets("Dashboard").Range("F6")
If RepFiles Is Nothing Then
Call FilestoRepFiles ' open files and save to collection
Else:
r = MsgBox("Click No to close them and define a new set", vbYesNo, "Work with the same set of files?")
If r = vbNo Then
Call CloseRepFiles ' close existing file set
Call FilestoRepFiles ' open files and save to collection
End If
End If
Application.ScreenUpdating = False
'remove any sheet with same search name
Application.DisplayAlerts = False 'prevent create alert
For Each ws In ThisWorkbook.Sheets
If ws.Name = ReportNm Then ws.Delete
Next
Application.DisplayAlerts = True
' add new sheet for results, with search name and set for results
ThisWorkbook.Sheets.Add(After:=Sheets("Dashboard")).Name = ReportNm
Set wbReport = Worksheets(ReportNm)
'Call Clear_data
Set wbMaster = ActiveWorkbook
For n = 1 To RepFiles.Count
' search file (removing path using Dir)
Set wbSlave = Workbooks(Dir(RepFiles(n)))
Call Data_Search(wbMaster, Worksheets(ReportNm), wbSlave)
Next n
wbMaster.Activate
ActiveWindow.Visible = True
Application.ScreenUpdating = True
End Sub
which calls the DataSearch macro (extract shown below, just the chnages in bold):
Sub Data_Search(wbMaster As Workbook, wbReport As Worksheet, wbSlave As Workbook)
.
.
.
reportDataColumnStart = 1 '### changed
searchValue = wbReport.Name
For Each ws In wbSlave.Worksheets
.
.
nextRow = wbReport.Cells(Rows.Count, reportDataColumnStart).End(xlUp).Row + 3
wbReport.Range(wbReport.Cells(nextRow, reportDataColumnStart), wbReport.Cells(nextRow + UBound(datatoShowArray, 1) - 1, reportDataColumnStart + dataColumnWidth)).Value = datatoShowArray
End If
Next ws
End Sub
There's also a macro to clear up when a new set of files is needed:
Sub CloseRepFiles()
On Error Resume Next
If RepFiles.Count = 0 Then Exit Sub
For n = 1 To RepFiles.Count
' close file
Workbooks(Dir(RepFiles(n))).Close savechanges:=False
Next n
' clear the collection
Set RepFiles = New Collection
End Sub
You'll need to reapply data validation to F4 in
Dashboard.
On downside to this file is that any (hidden) open files aren't closed on closing this file (though they will be closed if you close Excel). That's because RepFiles isn't available to a Workbook_BeforeClose event macro (otherwise Excel could neatly close down only the files it is using).
Hope this works for you.
