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,

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

Thanks.

please help with a solution - struggling to put a macro in place.

 

[mikejvir]

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

The Macro I sent is generic and is a starting point for you. I don’t personal time to write all the code for you. This macro will read in all 4000 files from one folder (assuming you have enough memory). Therefore, after reading all the files in, you can then look, search, rename, and move tabs to other worksheets.

You will have to work on this part of the code yourself. It is just strings and commands to do all the work.

Here is an outline.

For TestLoop = 1 to 4000
  Create new workbook as TestLoop & “.xlsx” and set for DestBook
  
  For SheetLoop = 1 to “X”   ‘  I have no idea what this could be
    SheetName = TestLoop & “_test” & SheetLoop
    If WS_Exists(SheetName)  then
     Sheets(SheetName).Move Before:=DestBook.Sheets(1)
     DestBook.Sheets(SheetName).Name = “test” &SheetLoop
    Else if
      LEAVE LOOP for SheetLoop
    End If
  Next SheetLoop
   Close DestBook1
Next TestLoop

Public Function WS_Exists(ByVal WorksheetName As String) As Boolean

On Error Resume Next
WS_Exists = (Sheets(WorksheetName).Name <> "")
On Error GoTo 0

End Function

 

[mikejvir]

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

Oops. Once correction dealing with the workbook.

For TestLoop = 1 to 4000
  Create new workbook as TestLoop & “.xlsx” and set for [COLOR="#FF0000"]NewBook[/COLOR]
  
  For SheetLoop = 1 to “X”   ‘  I have no idea what this could be
    SheetName = TestLoop & “_test” & SheetLoop
    If WS_Exists(SheetName)  then
     Sheets(SheetName).Move Before:=[COLOR="#FF0000"]NewBook[/COLOR].Sheets(1)
     [COLOR="#FF0000"]NewBook[/COLOR].Sheets(SheetName).Name = “test” &SheetLoop
    Else
      LEAVE LOOP for SheetLoop
    End If
  Next SheetLoop
   Close [COLOR="#FF0000"]NewBook[/COLOR]
Next TestLoop