One cell from several sheets to one column

  • Thread starter Thread starter Kel
  • Start date Start date
K

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
 
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.
 
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
 
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
 
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??
 
Back
Top