One cell from several sheets to one column

[Kel]

I have a spreadsheet with over 200 tabs/worksheets. I want to copy the
same cell from every worksheet into one sheet into a single column.
The worksheets have unique names. Is a macro the easiest way to move
the data? If so, how can I set the macro up to go through each tab, so
I don't have to go to each tab and run the macro separately?

Thanks,
Kel

 

[Ken Wright]

Examples of sheet names and what cell you want?

 

[Kel]

The sheets are named with unique loan numbers, ie 0157654 and 5458467.
The cells are text, in cell A5. I want to create a new worksheet/book
called All. The A1 value in All would be equal to A5 in 0157654, the
A2 value in All would be equal to A5 in 5458467, etc.

 

[Ken Wright]

Do you have a list of those account numbers anywhere other than on those
tabs. If so and you can dump a list of them onto your new sheet, then take
a look at the INDIRECT function, eg

Assuming you the account number
5458467

and you had a tab named like that, then you could reference A5 like
='5458467'!A5

or assuming you had your list of account numbers in Col B starting B1, then
in A1 you had

=INDIRECT("'"&B1&"'!A5") and copied down

 

[Gord Dibben]

Kel

Sub Copy_Data()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set CopytoSheet = Worksheets.Add
CopytoSheet.Name = "Copyto"
For Each w In ActiveWorkbook.Worksheets
w.Range("A1").Copy Destination:=Sheets("CopyTo") _
.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Adjust "A1" to the cell in question.


Gord Dibben Excel MVP

 

[Kel]

I don't have a list of the loan numbers....Is there anyway to add the
tab names to this code? Also, since I have more than one cell I do
need to grab, how do I change the code to copy a second cell, ie A2,
into Column B? In addition, there is a third cell I'd like to grab,
but it varies on each sheet. The common bond between them is its in a
column where in the top cell the word Balance is entered. I need the
last cell in the column. I'm thinking a macro can handle doing a find,
then end down to find this value, Right??