Excel 2003 - Combining info from several pages to one page

  • Thread starter Thread starter Kelly
  • Start date Start date
K

Kelly

I have a 400 page spreadsheet and I just want the info from box A2 and F40
from each page to appear in two columns on a seperate page. A2 on each page
is a Part # and F40 is our current inventory. Does anyone know the equation
and how to set up this 'all' inventory page?

Thanks,
 
Assuming a "page" is a worksheet and you have 400 worksheets in a single
workbook.........

How are the sheets named?

If named Sheet1, Sheet2 etc.

On a summary sheet enter this formula in A1

=INDIRECT("Sheet" & (ROW()) & "!A2") and copy down to A400

In B1 enter =INDIRECT("Sheet" & (ROW()) & "!F40")

Copy down.

If sheets are uniquely named VBA will be rquired.

Sub ListCells()
Dim Cell As Range
Dim sh As Worksheet
Dim csh As Worksheet
Set csh = ActiveWorkbook.Worksheets.Add
csh.Name = "Summary"
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> csh.Name Then
With csh.Range("a65536").End(xlUp).Offset(1, 0)
.Value = sh.Range("A2").Value
.Offset(0, 1).Value = sh.Range("F2").Value
End With
End If
Next sh
End Sub



Gord Dibben MS Excel MVP
 
Back
Top