VBA to merge specific sheet of different workbooks into one multisheet master file

[tassu7860]

I have multiple workbooks as individual office KPIs with first sheet as linked to other sheets in that workbook. There are 42 of these workbooks. I now want to create a master file with only Sheet 1 of all 42 workbooks that i place under under one folder. The master file will have individual sheets with Sheet 1 of each of these 42 workbooks. Can someone please help. Also, all 42 are macro enabled workbooks, will that cause any issue?

[Jan Karel Pieterse]

Have you tried Data, New Query (or Get Data), From File, From Folder ?

[snb]

You might use:


Sub M_snb()
for j=1 to 42
thisworkbook.sheets.add ,thisworkbook.sheets(thisworkbook.sheets.count),,"G:\OF\example" & j & ".xlsm"
next
End Sub

[tassu7860]

Where should I post this. I have 42 workbooks each with may be N number of sheet, but I only want to copy first sheet from each of these 42 workbooks.



You might use:


Sub M_snb()
for j=1 to 42
thisworkbook.sheets.add ,thisworkbook.sheets(thisworkbook.sheets.count),,"G:\OF\example" & j & ".xlsm"
next
End Sub

[tassu7860]

Yes I did but as the sourced files are VBA enabled, this didnt work.

[Jan Karel Pieterse]

Not sure what the fact that they are vba enabled has to do with our suggestions?

[tassu7860]

Hey Jan.

This code didnt work for me. I mean the files are VBA and has linked pages


Have you tried Data, New Query (or Get Data), From File, From Folder ?

[Jan Karel Pieterse]

Regardless of VBA or links, the Power Query route should work.

[RhonKiser]

I'm looking for something similar to this.

I have several similar workbooks. I want to copy specific worksheets from each workbook and paste into 1 workbook. tab names can be the same with just a # on the end.