Combine MANY tabs (with single column) into SINGLE tab.

Wonder if someone knows a way to do this, I'm using Excel 2007 (running on Windows 7):

- I have a single workbook with something like 173 or so tabs.
- Each tab has only Column A populated.
- On each tab, Column A is populated with anywhere from 1 to 30 (max) email addresses (it varies from tab to tab).
- There are no spaces between email adresses, that is, on a tab where there are 2 of them, they are in cells A1 and A2, etc.
- There are no headers of any kind.
- The result I need is a new tab with a single column that contains ALL of the email adresses (don't care if the individual tabs are erased or not).

I don't want to use consolidate since I don't want to have to select each tab and select the data...it'll take forever and I have other workbooks like this.

Might anyone know of a way to do this automatically?

I'll admit, I don't know ANYthing about vba, so if that's the solution, you'll have to be detailed and explicit (sorry about my ignorance).

Very appreciative in advance for any help,
Nick

My VB is a little rusty, but you could use concatenation or indirect to systematically write a formula to pull it.

Do the sheets follow any sort of naming convention, or are they just Sheet1, Sheet2, etc?

Ok Nick

This is quite interesting so I will write you a program to do this.

BRB.

I'm Back.

Your Code is below.

Make sure you are in the workbook that you want to modify.

Make a note of the Macro name at the next stage
Click on "Developer" and then "Record Macro" Make sure you select in this workbook.

Click on Cell A1.

Click on "Developer" and then "Stop Recording"

Click on "Developer", Select your macro and then "Edit"

Now paste the following code over your macro. and close visual basic.


Ok now

Click on "Developer" and then "Run Macro" using my macro name "ModifyMacro"


********************************************************************************************

Please Login or Register  to view this content.

if "Summary" worksheet doesn't already exist it is created. col A of all other sheets copied to col A in "Summary"

Please Login or Register  to view this content.

It gave me error codes and stopped. But someone gave me the following, and it worked nicely:

Sub wigi()

With ActiveWorkbook

Set ws = .Sheets.Add(before:=.Sheets(1))

For i = 2 To .Sheets.Count
With .Sheets(i).UsedRange
ws.Cells(ws.Rows.Count, 1).End(xlUp)(2).Resize(.Rows.Count).Value = .Value
End With
Next
ws.Cells(1).Delete xlShiftUp

End With


End Sub