Macro to open several Files

[howard]

I have a macro to open a workbook using a wilcard Character


I would like the code amended to open 3 workbooks starting with

The workbooks to be opened are all .xlsm workbooks

Bauten East
Prolen South
Prolem North


Eg Bauten East Sales Oct 2022.xlsm


I need a wildcard Character so to limit the files to be selected in "C:\Sales Reports by month"


Your assistance is most appreciated

 Sub Open_Workbook()

Dim xfile As Variant

Dim wb2 As Workbook


Application.ScreenUpdating = False



ChDir ("C:\Sales Reports by month")

With Application.FileDialog(msoFileDialogFilePicker)

.ButtonName = "Open"

.Filters.Clear

.Filters.Add "Excel Files", "*.xlsm"

.InitialFileName = "*Bauten East*.xlsm"

.Title = "Select File"

If .Show <> -1 Then Exit Sub

For Each xfile In .SelectedItems

Set wb2 = Workbooks.Open(xfile)

wb2.Sheets(2).UsedRange.Copy _

ThisWorkbook.Sheets("Imported Data").Range("A" & Rows.Count).End(xlUp).Offset(1)

wb2.Close False

Next

End With

ChDir ("C:\Sales Reports")

Application.ScreenUpdating = True

End Sub

 

[HongRu]

I don't really know what you want is Sheets("sheet2") or Sheets(2).
Sheets("sheet2") refers to the sheet named "sheet2".
Sheets(2) refers to the second sheet.
The below code use Sheets("sheet2").
Good luck.

Sub sbOpenFiles()
    Application.ScreenUpdating = False
    Dim myPath
    myPath = "C:\Sales Reports by month\"
    
    Dim myFileName
    myFileName = Dir(myPath)
    
    Dim myCheck
    Do Until myFileName = ""
        If InStr(1, myFileName, ".xlsm") Then
            myCheck = InStr(1, myFileName, "Bauten East") + InStr(1, myFileName, "Prolen South") + InStr(1, myFileName, "Prolem North")
            If myCheck Then
                Application.Workbooks.Open myPath & myFileName
                Workbooks(myFileName).Sheets("sheet2").UsedRange.Copy Destination:=ThisWorkbook.Sheets("Imported Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
                Workbooks(myFileName).Close
            End If
        End If
        myFileName = Dir()
    Loop
End Sub

 

[howard]

Many Thanks HongHu

Your code works 100%