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]

When there are multiple undisciplined sources being fed to a central point it is difficult to process those sources without some method to organize the input into a uniform structure so that it can be logically addressed with programming methods. VBA does not handle arbitrary input very well when a user has the option of doing it their way. A good system would be regulated so that all users do things the same way and in the same format using prescribed naming conventions, etc. Otherwise, you will have the problem you have encountered.

 

[austinandreikurt]

The worksheets were named based on products listed in cell B3 to B & lastrow of the tracker tool but it will not always be applicable for each user thus the tool will only create a worksheet that is applicable to them so when both users are having F1 product, then both of them will have that sheet. If I can't get the flexible codes to run on each sheets for possible similiarities from another workbook, then it will be my last resort to create all product sheets for all users.

 

[JLGWhiz]

I don't think I can be of much further help on this so I will drop off the thread. Perhaps someone else will take a look at it and offer some advice.
Regards, JLG

 

[austinandreikurt]

Still many thanks JLGWhiz! If I can't find help until later, I will then just use my last resort.