VBA to Consolidate multiple files from Folder into 1 workbook and only copy certain worksheet with specific name from the excel files.

[Jnt]

Hi all the macro expert,
I need to consolidate multiple files from 1 folder yet the each workbooks consists of 4-5 worksheets which I only need the data from 1 particular worksheets from each workbooks( the rest like index or other worksheet name will be excluded).
Currently I am using the below VBA to consolidate the file but it will also include all the worksheets into 1 workbooks which I do not really need all the worksheets into a workbooks. . Hence I would have to manually delete the worksheet one by one which time consuming. Can you help to look at my below VBA and help to edit or modify the code that can help me to exclude the certain worksheet name OR only include the worksheet name that I want. whichever way may help. Many Thanks

Sub CombineFiles()

Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet

Application.EnableEvents = False
Application.ScreenUpdating = False
Path = "C:\Users\jlsh\Documents\Power Query" 'Change as needed
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
For Each WS In Wkb.Worksheets
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next WS
Wkb.Close False
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

 

[alansidman]

An alternative to VBA is Power Query

 

[Jnt]

An alternative to VBA is Power Query

I tried to explore the power query before but couldn't work as all worksheet format somehow got different and was unable to group them together. Or maybe I m too dumb to understand through video.