Consolidating Tabs and tab names

[athard]

Hi, I have a spreadsheet with multiple tabs. Each tab is named by company. In each tab, there is one column, zip code, with multiple rows of zips.

Is there a way to combine all into one tab, where there will be 2 columns. Column 1 will be the company names from each of the tabs and column 2 will be the corresponding zip code.

Eg.

Tab 1 name- ABC Company
Fields: Zip Code
50026
50311

Tab 2 name - XYZ Company
Fields: Zip Code
66125
66127
66584
66258

FINAL RESULT:

Company Name Zip Code
ABC Company 50026
ABC Company 50311
XYZ Company 66125
XYZ Company 66127
XYZ Company 66584
XYZ Company 66258

Please advise. Thank you in advance.

[johncassell]

Should be simple enough but do you want to attach a sample sheet so we know exactly what the data looks like (You may have column headers etc.. that aren't mentioned)

Thanks

[athard]

Thanks, sample spreadsheet attached.

[johncassell]

try the attached. Feel free to post back if theres anything you don't understand.

John

[athard]

try the attached. Feel free to post back if theres anything you don't understand.

John

Thanks, but I think I need some help.

My original worksheet has 185 tabs.

here is what I did:

I created a new tab and named is "Summary".

I copied the macro over and ran it.

It didn't do anything. Some values flashed for a milisecond and then went away. Am I missing a step?

By the way, the macro works well on the test file that you uploaded.

Thanks.

[stanleydgromjr]

athard,

Detach/open workbook CreateSummary - consolidate tabs - athard - EF774596 - SDG12.xls and run the CreateSummary macro.



If you want to try the macro on another workbook:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Then run the CreateSummary macro.



If the macro does not work correctly based on your sample workbook, then please post another workbook whose structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

[athard]

Stanley, thanks for the detailed steps. I tried what you told me in the workbook. (Option 2). It gave me a Runtime Error 1004, Application Defined or object defined error. When I clicked on End, it worked for the first 29 tabs only and then it stopped. I reviewed all 185 tabs and the data format and column name is the same. Please advise.

Also, thanks for your help, I am much closer now.

[stanleydgromjr]

athard,

Please attach another workbook containg two worksheets that did work, and several worksheets that did not work (sensitive data scrubbed/removed), especially the next workworksheet (moving left to right on the tabs view) that did not show up in worksheet Summary, column A, Company Name.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

[athard]

Here you to. New spreadsheet attached.

Thanks again.

[realniceguy5000]

Hi, Maybe something like this will work?


Mike

Please Login or Register  to view this content.

[athard]

Thank you so much for all your help. This goes out to everyone who replied.

@realniceguy-This works perfectly. You have no idea how much this helps.

Thanks again.

[stanleydgromjr]

athard,

Based on your latest posted workbook:
See the two worksheet tabs color RED.

Worksheet "Rainbow Paint 11702" had Title "Zip Code" in cell B1.
Macro looks for "Zip Code" in row 1.
The macro processed these Zip Codes.

Worksheet "California Flooring 97189" "Title 'Zip Code' was not found in row 1".
Message in worksheet Summary, column A and B.


Detach/open workbook CreateSummaryV2 - consolidate tabs - athard - EF774596 - SDG12.xlsm and run macro CreateSummaryV2.



If you want to use the macro on another workbook:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


[b[Then run the CreateSummaryV2 macro.[/b]