Import all active wrksheets from multiple wrkbooks

[jwh]

I have a folder containing a variable number of workbooks. I want to copy all of the active worksheets from those workbooks into a summary workbook. Then I will use code from Ron D that will create a summary worksheet.

I have not been able to find something that actually gets all active worksheets and imports them to a workbook - please help

Jay

 

[Trevor G]

Jay welcome to the forum,

Take a look at this code to see if it helps.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Open_My_Files()<br><SPAN style="color:#00007F">Dim</SPAN> MyFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> MastWB  <SPAN style="color:#00007F">As</SPAN> Workbook<br><SPAN style="color:#00007F">Dim</SPAN> SalesWB <SPAN style="color:#00007F">As</SPAN> Workbook<br><SPAN style="color:#00007F">Set</SPAN> MastWB = Workbooks("MasterWorkbook") <SPAN style="color:#007F00">'change to suit</SPAN><br>MyPath = "M:\Access Files\"<br>MyFile = Dir(MyPath)<br><br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> MyFile <> ""<br><SPAN style="color:#00007F">If</SPAN> MyFile <SPAN style="color:#00007F">Like</SPAN> "*.xls" <SPAN style="color:#00007F">Then</SPAN><br>Workbooks.Open MyPath & MyFile<br><br>    <SPAN style="color:#00007F">Set</SPAN> SalesWB = ActiveWorkbook <SPAN style="color:#007F00">'change to suit</SPAN><br>    SalesWB.Sheets.Copy Before:=MastWB.Sheets(MastWB.Sheets.Count)<br>ActiveWorkbook.Close <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>MyFile = Dir<br><SPAN style="color:#00007F">Loop</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[jwh]

I tried this code and it gives me a subscript out of range for the masterworkbook or the mypath - although I copied them identical

 

[Trevor G]

Do you have Option Explicit at the top of the module if so you have to declare myPath as String. Add this in near the top

Dim myPath as String