Consolidate Worksheets within Different Workbooks

[austinandreikurt]

I need some help on how to code. I have several files to consolidate into one file. And this will be thru the Workbooks.Open method and each file consist of different sheets that may or may not exist on the other files. Say for example I have Sheets("F1") in the workbook1; Sheets("F1") and Sheets("IG") in workbook2; and Sheets("F2") and Sheets("ENS") in workbook2. So when they consolidate, the consoworkbook should consist of 4 tabs namely Sheets("F1"), Sheets("F2"), Sheets("IG") and Sheets("ENS"). Below is what I have coded for now but haven't organized my thought on how to fully construct the consolidation.

    Dim my_FileName As Variant
        my_FileName = Application.GetOpenFilename(FileFilter:="Excel Files,*.xls*")
   
    If my_FileName <> False Then
        Workbooks.Open Filename:=my_FileName
        wbook = ActiveWorkbook.Name
    End If
   
    For ???
        On Error GoTo NextSheet
            WorksheetExists = (ActiveWorkbook.Sheets(WorkSheetName).Name <> "")
    Next
   
NextSheet:
    Resume Next

I know this is a bit disorganized and would really appreciate your help on this. Thanks!

 

[JLGWhiz]

Since you are using Application.GetOpenFileName, opening the workbook is easy enough. the next thing you need to do is set a variable for your sheet names and I would suggest using Select Case to do that: Example

Select Case my_FileName
    Case "Data.xlsx"
         Set sh = Sheets("myDataSheet")
    Case "Input.xlsx"
         Set sh = Sheets("myInputSheet")
End Select
With sh
    'Code to do stuff
End With

But I find it easier to work with some type of algorithm like Select Case or If...ElseIf...EndIf than try to use the error trapping method. once the algorithm has run your variable is good for the rest of the code until you close that workbook and start the next one.

 

[austinandreikurt]

Hi JLGWhiz,

Yes, this is exactly what I was thinking but I literally don't know how am I supposed to trap the error case when a sheet is non-existent to the other. Can you provide a sample say on Sheets("F1") in the workbook1; Sheets("F1") and Sheets("IG") in workbook2 on how a proper coding should be done. Let's say I will be using Sheet1 as the base or the consolidation workbook since it is the very first file I have opened, it just need to combine the Sheets("F1") in the workbook2 into the next blank row in Column A of Sheets("F1") of workbook1. And since there is no Sheets("IG") in workbook1, it will automatically just move and copy that worksheet. So the final output will be a Sheets("F1") and Sheets("IG") in workbook1. Then it will make the UserForm4.Show method again to ask the user if she still want to do another consolidation or not but this part is easy now. I just need the trapping of whether those sheets are existing to each workbook or not without using the error handling codes. Its like If sh exist then code else end if but I do not know the proper algorithm.

 

[JLGWhiz]

Is Workbook1 and Workbook2 the actual names of the workbooks. If not then the code would falter because the file you open the FileOpen dialog box will be different.

 

[austinandreikurt]

No. That is why I set the wbook = ActiveWorkbook.Name for the variable. But I am not sure how to set for the next one. But there will always be 2workbooks to work on at everytime.

 

[austinandreikurt]

So its like for the first run of the code, we set wbook1 as the first file to open thru open method then the For Next Case/If..Then..EndIf will run a continuous flow of:
1. Show UserForm4 to ask if user would like to consolidate another file, if yes
2. FileOpen method to open another workbook and set the name of the new workbook as wbook2 variable
3. Start consolidation by checking of sheets which will consist of 3cases that will run throughout the worksheets of both wbook1 and wbook2:
3.1 If sheet exist in wbook1, then paste the data of the same sheet from wbook2 to the first non-blank row in column A of sheet in wbook1
3.2 If sheet exist on wbook1 but no sheet on wbook2, then nothing to do
3.3 If sheet do not exist on wbook1 but exist in wbook2, then the sheet.copy method will be done so it will also exist in wbook1
4. NEXT which will go back to Step1 to prompt UserForm4 to ask the user until user choose the button STOP.
The UserForm4 is already set-up so what I just really needed is for STEPS 2 and 3 to be coded. I hope this explanation can help. Thanks!

 

[austinandreikurt]

Just to add, after Step 3.2, wbook2 will be closed with no changes to be save and the next workbook to be opened using the FileOpen method right after the UserForm4 shows will have again the variable wbook2 as the name for this newly opened workbook. And this process will continuously run until user click on the cancel button of UserForm4 when it prompts again. Thank you. So it short, wbook1 never changes, and wbook2 will always be the variable name for the opened workbooks after wbook1.

 

[austinandreikurt]

Is this really something difficult to achieve? I just really need the If Then ElseIf statement in STEP3 to check the existence of each worksheets without using the error handling codes to trap the errors. The codes that I am thinking can be structured like this but ofcourse other'layout is okay as long as it works like below:

     my_FileName = Application.GetOpenFilename(FileFilter:="Excel Files,*.xls*")

    If my_FileName <> False Then

        Workbooks.Open Filename:=my_FileName
        wbook1 = ActiveWorkbook.Name

        For 'each OKAY on Userform4.Show
       
            UserForm4.Show 'I will have the command of OKAY here since it is just FileOpen method
            my_FileName = Application.GetOpenFilename(FileFilter:="Excel Files,*.xls*")

                If my_FileName <> False Then

                    Workbooks.Open Filename:=my_FileName
                    wbook2 = ActiveWorkbook.Name

                Case wbook1
                    For Each Sheet In wbook1
                        If sheet exist in wbook2 then
                            ''I can do the code to copy from worksheet of wbook2 to wbook1
                        ElseIf sheet do not exist in wbook2 then
                            ''Can be omitted as there is nothing to do in here
                        End If
                    Next Sheet
                Case wbook2
                    For Each Sheet In wbook2
                        If sheet do not exist in wbook2 then
                            ''I can do the code to move and copy the worksheet from wbook2 to wbook1
                        End If
                    Next Sheet
                   
            Next ''It will go back to UserForm4 again at the start of this For-Next
                           
    End If

 

[JLGWhiz]

Since the sheet names apparently are not in any set order and can vary depending on user, maybe using an Input box and error trap would be better. Here is an example of that method.

Recyc:
shName = InputBox("Enter name of sheet to copy", "Sheet to copy")
On Error Resume Next
    If Sheets(shName) Is Nothing Then
        If Err.Number > 0 Then
            Err.Clear
            ans = MsgBox(shName & " Not Found!", vbYesNo, "Continue?")
            If ans = vbNo Then Exit Sub
            GoTo Recyc:
            End If
    Else
        Sheets(shName).Copy After:=Workbook1.Sheets(Workbook1.Sheets.Count)
    End If
On Error GoTo 0

It uses the Recyc: label to allow the user to change sheet names if a name they enter is not found in that workbook. If the name is found, then it will just copy th sheet to the end of workbook1.
It has a messate box that allows the user to exit the sub when they have exhausted all sheet names and none were found.

 

[austinandreikurt]

Hmm. It might be too much for the user doing the consolidation as it may consist of 20 sheets in a workbook. I actually have an alternative which is to create all sheets in the file before it will be saved by each user and then consolidated. It was actually a created file from a tracker tool used by different users and the team lead will collect all the summary files from different users and they are about 20 in a team. All this process is within a tracker tool but only the team lead's tracker tool have the function to consolidate. So basically, each user use different channels and that is 1 channel per sheet. If there is no solution I will create all channels for each user in the file even if they are blank but I want this as my last resort as it can result in unforeseen errors.