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

Examples of sheet names and what cell you want?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Kel" wrote in message
oups.com...
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

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 wrote:
Examples of sheet names and what cell you want?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
:-)

----------------------------------------------------------------------------

"Kel" wrote in message
oups.com...
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

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Kel" wrote in message
oups.com...
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 wrote:
Examples of sheet names and what cell you want?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission
:-)

--------------------------------------------------------------------------
--

"Kel" wrote in message
oups.com...
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

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


On 16 Feb 2005 15:55:51 -0800, "Kel" wrote:

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

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??