Excel 2003 - Combining info from several pages to one page
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
On Wed, 21 Oct 2009 08:08:01 -0700, Kelly
wrote:
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,
|