Extract one cell from each page

[daffy]

Hi all

Is it possible to extract data from a single cell from a workbook with many pages?

For an example i have a workbook that has over 100 pages of data and wish to extract the data from the same cell in each page.

From each page i am looking to extract cell A10 and place all the data in a front sheet call "Front sheet" and have the data vertical in A1 to A100.
Then do the same with cell D20 from each page and have this data vetical in B1 to B100

Hope that this makes sense.

Cheers

Don

 

[James006]

Hi Don,

The solution is to use the function Indirect() with all the names of your worksheets being listed in your "Front sheet" ...

HTH

 

[James006]

Let me give you an example :

=INDIRECT("'"&firstsheetname&"'!A10")

HTH

 

[VoG]

Try

Sub test()
Dim ws As Worksheet, j As Long
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Front Sheet" Then
        j = j + 1
        With ws
            .Range("A10").Copy Destination:=Sheets("Front Sheet").Range("A" & j)
            .Range("D20").Copy Destination:=Sheets("Front Sheet").Range("B" & j)
        End With
    End If
Next ws
End Sub

 

[daffy]

Thanks for the replys but, Sorry guys both didnt seem to work or is it somthing im not doing right.

Don

 

[James006]

Hi again

VoG ' s answer is a macro you have to insert in a module (alt F11)

The other option is a simple function ...

Can you expand a bit furhter on your difficulties ...

 

[daffy]

James
I have now made a simple workbook with only three sheets.
"Front Sheet" and the other two sheets called Sheet2 and Sheet3

In Sheet2 i have put my name in Cell A10
In Sheet3 i have put my sir name in Cell A10

If i paste you formula into the "Front Sheet" it does not pull through any data.
If i paste VOG Mod in to a VBA "this workbook" it does not pull any data through
If i paste VOG Mod in to a VBA "Front Sheet" it does not pull any data through

Does this explain it any better?

Cheers

DON

 

[James006]

Don,

Let me try to go step by step :

1. In your "Front Sheet", say in cell A2, type Sheet2

2. In cell A3, type =INDIRECT("'"&A2&"'!A10")

As a result, you should get the amount located in cell A10 in the worksheet named : Sheet2

Regarding the VBA solution, once you are located in VBA (alt F11), you must :

1. From the Menu, click on Insert > Module

2. Once Module 1 is created, double-click on it to open the code area where you can copy VoG 's macro

HTH