Combining Multiple Sheet to One Workbook - Loop Function

[avid.excel.user]

Hi Friends,

I am struggling to search a piece of excel macro which can do the following steps for me :

1. I have individual excel sheets in 3 folders where the name of the file is the same and an unique identifier
for example : 1_test1.xlsx in folder C:\Macro\test1; 1_test2.xlsx in folder C:\Macro\test2; 1_test3.xlsx in folder C:\Macro\test3

2. Such a way i have excel sheets which are ranging from 1 to 4000

3. Each excel sheet has only 1 sheet named test1; test2; test3

4. Macro is needed to
a. Open the directory
b. create a new workbook with is 1.xlsx
c. open the worksheet 1_test1.xlsx
d. copy all data in test1 sheet from 1_test1.xlsx and paste it into 1.xlsx in sheet test1
e. close the worksheet 1_test1.xlsx
f. open the worksheet 1_test2.xlsx
g. copy all data in test2 sheet from 1_test2.xlsx and paste it into 1.xlsx in sheet test2
h. close the worksheet 1_test2.xlsx
i. open the worksheet 1_test3.xlsx
j. copy all data in test3 sheet from 1_test3.xlsx and paste it into 1.xlsx in sheet test3
k. close the worksheet 1_test3.xlsx
l. close 1.xlsx

re-run this from 1 to 100

Please help with a code, since i am breaking my head over it and still not found any thing related to this.

Thank You

 

[avid.excel.user]

Re: Combining Multiple Sheet to One Workbook - Loop Function - Urgent !!! Please Help !!!

Hi Friends,

I am struggling to search a piece of excel macro which can do the following steps for me :

1. I have individual excel sheets in 3 folders where the name of the file is the same and an unique identifier
for example : 1_test1.xlsx in folder C:\Macro\test1; 1_test2.xlsx in folder C:\Macro\test2; 1_test3.xlsx in folder C:\Macro\test3

2. Such a way i have excel sheets which are ranging from 1 to 4000

3. Each excel sheet has only 1 sheet named test1; test2; test3

4. Macro is needed to
a. Open the directory
b. create a new workbook with is 1.xlsx
c. open the worksheet 1_test1.xlsx
d. copy all data in test1 sheet from 1_test1.xlsx and paste it into 1.xlsx in sheet test1
e. close the worksheet 1_test1.xlsx
f. open the worksheet 1_test2.xlsx
g. copy all data in test2 sheet from 1_test2.xlsx and paste it into 1.xlsx in sheet test2
h. close the worksheet 1_test2.xlsx
i. open the worksheet 1_test3.xlsx
j. copy all data in test3 sheet from 1_test3.xlsx and paste it into 1.xlsx in sheet test3
k. close the worksheet 1_test3.xlsx
l. close 1.xlsx

re-run this from 1 to 100

Please help with a code, since i am breaking my head over it and still not found any thing related to this.

Thank You

No solution yet !

 

[avid.excel.user]

Re: Combining Multiple Sheet to One Workbook - Loop Function - Urgent !!! Please Help !!!

No solution yet !

Please help !

 

[oldbrewer]

Re: Combining Multiple Sheet to One Workbook - Loop Function - Urgent !!! Please Help !!!

Each excel sheet has only 1 sheet

an excel workbook has one or more sheets
workbooks live in folders

Such a way i have excel sheets which are ranging from 1 to 4000
do you mean there are 4000 rows in each sheet ?

 

[oldbrewer]

Re: Combining Multiple Sheet to One Workbook - Loop Function - Urgent !!! Please Help !!!

are you wanting a new workbook with 3 sheets which contain the sheets from 3 different workbooks ?

 

[avid.excel.user]

Re: Combining Multiple Sheet to One Workbook - Loop Function - Urgent !!! Please Help !!!

are you wanting a new workbook with 3 sheets which contain the sheets from 3 different workbooks ?

Hi,

Yes, exactly. The new workbook 1.xlsm should consist of 3 sheets from 3 different workbooks.

Thanks.

 

[mikejvir]

Re: Combining Multiple Sheet to One Workbook - Loop Function - Urgent !!! Please Help !!!

Here is a macro that will read in multiple files but they are in one folder.

Public Sub OpenMultipleFiles()
Dim DestBook As Workbook
Dim fn As Variant, f As Integer
Dim DisplayString As String
'       Open up a multi select file window
  fn = Application.GetOpenFilename(, 1, "Select one or more files to open", , True)
  If TypeName(fn) = "Boolean" Then Exit Sub   '   Exit if no file selected
  Application.ScreenUpdating = False          ' do all transfers in the background
  Application.DisplayStatusBar = True
  For f = 1 To UBound(fn)
    Workbooks.Open fn(f)                      '   open the file on a new workbook
    If f = 1 Then                             '   1st one opened is the home page
      Set DestBook = ActiveWorkbook
    Else                                      '   copy every page after #1 to workbook #1
      Application.StatusBar = "Adding file number   <<  " & f & "  >>"
      Sheets(1).Select
      Sheets(1).Move Before:=DestBook.Sheets(1)
    End If
  Next f
  Application.StatusBar = ""
  Application.ScreenUpdating = False
End Sub

You will have to find some way to read the filenames into the array fn.


Mike Virostko

 

[avid.excel.user]

Re: Combining Multiple Sheet to One Workbook - Loop Function - Urgent !!! Please Help !!!

Hi

There are 4000 such workbooks in the same folder.

For example: the following workbooks with sheet names are stored in a folder C:\Macro

1_test1.xlsx Sheet Name - test1
1_test2.xlsx Sheet Name - test2
1_test3.xlsx Sheet Name - test3
1_test4.xlsx Sheet Name - test4
1_test5.xlsx Sheet Name - test5

Then there are other workbooks where the sheet name are the same as above but they are from 2 to 4000 i.e.

2_test1.xlsx Sheet Name - test1
2_test2.xlsx Sheet Name - test2

etc...

The Macro should create a new work book which is 1.xlsx; 2.xlsx;3.xlsx etc.. till 4000.xlsx

Each of these workbooks should have sheets test1, test2, test3, test4, test5 from the resp excel workbooks for example:

1.xlsx with sheets
test1 from 1_test1.xlsx
test2 from 1_test2.xlsx
test3 from 1_test3.xlsx
test4 from 1_test4.xlsx
test5 from 1_test5.xlsx

This is the solution i am looking for ...

 

[avid.excel.user]

Re: Combining Multiple Sheet to One Workbook - Loop Function - Urgent !!! Please Help !!!

Hi Mike..

Thank you for the reply. The solution is not working the way enumerated in the above thread. Kindly refer the below:

Hi

There are 4000 such workbooks in the same folder.

For example: the following workbooks with sheet names are stored in a folder C:\Macro

1_test1.xlsx Sheet Name - test1
1_test2.xlsx Sheet Name - test2
1_test3.xlsx Sheet Name - test3
1_test4.xlsx Sheet Name - test4
1_test5.xlsx Sheet Name - test5

Then there are other workbooks where the sheet name are the same as above but they are from 2 to 4000 i.e.

2_test1.xlsx Sheet Name - test1
2_test2.xlsx Sheet Name - test2

etc...

The Macro should create a new work book which is 1.xlsx; 2.xlsx;3.xlsx etc.. till 4000.xlsx

Each of these workbooks should have sheets test1, test2, test3, test4, test5 from the resp excel workbooks for example:

1.xlsx with sheets
test1 from 1_test1.xlsx
test2 from 1_test2.xlsx
test3 from 1_test3.xlsx
test4 from 1_test4.xlsx
test5 from 1_test5.xlsx

This is the solution i am looking for ...

 

[avid.excel.user]

Re: Combining Multiple Sheet to One Workbook - Loop Function - Urgent !!! Please Help !!!

Please help with a solution !!!