Copy the same cell from numerouse pages

[the-jackal]

What i am trying to do is copy the same cell from about 50 pages within
the same workbook into a column.

ie

Column A Names all the pages 001-50
Column B Has data from Cell B5 on every page.

I have used ='001'!B5 which works fine but i cant seem to copy it down
the list. All it changes is the cell. I want the Cell to stay the same
but the page to change.

 

[Kevin B]

Insert a worksheet and name it Total. Then press Alt+F11 to open the Visual
Basic Editor

Click on INSERT in the menu and selet MODULE. Copy the code below and paste
it into your blank module, and then locate the following lines of code:

wsTarget.Cells(iRow, 1).Value = _
ws.Range("B%").Value

change the second line that says ws.Range("B5").Value so that the cell is
the cell you wish to pick up.

Move back to Excel and then Click on TOOLS in the menu, select MACROS,
select MACRO. If necessary, highlight AllCellsVals in the list of available
macros and click RUN to execute the macro.

Sub AllCellVals()

Dim wb As Workbook
Dim ws As Worksheet
Dim iRow As Integer
Dim strSheetName As String
Dim strTarget As String
Dim wsTarget As Worksheet

iRow = 1
Set wb = ActiveWorkbook
strTarget = "Total"
Set wsTarget = wb.Sheets(strTarget)

For Each ws In wb.Worksheets
strSheetName = ws.Name
If strSheetName <> strTarget Then
wsTarget.Cells(iRow, 1).Value = _
ws.Range("B5").Value
iRow = iRow + 1
End If
Next ws

Set wb = Nothing
Set ws = Nothing
Set wsTarget = Nothing
Exit Sub

End Sub



Click on FILE and select SAVE AS and save the file under a new name so the
original data does not mangled should things go awry.

In your newly saved copy of the original, click on TOOLS, select MACRO,
select MACROS. Select AllCellValues, if necessary, and then click the RUN
button.

This will cylce through all the workbooks

 

[Ken Wright]

Assuming your sheet names start in cell A1, then in B1 put this and then
copy down:-

=INDIRECT(A1&"!B5")

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

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[the-jackal]

Thank you ever so much. This has really helped.

Thank you both.

 

[Ken Wright]

You're very welcome - glad it helped, and appreciate the feedback.

Regards
Ken.................