Retrieve the same cell ref from multiple sheets?

[Carl Jarvis]

Hello

I would have thought this to be easy but it has be stumped!

I have a workbook with 100 sheets (names sheet1 through 100) and I need to
retrieve the value (either text or number) from cell B2 in each sheet into a
new workbook.

I have tried copy / pasting the formula but it always refers to Sheet1, I
cannot seem to make the Sheet number increase?

I hope this makes sense and someone can help?

Cheers,

Carl.

 

[Lady Layla]

In the workbook with the sheets, group all the sheets (holding shift key down,
click on tab for sheet 1 and then click on tab for sheet 100)

In new workbook enter =sum( then go to other workbook, click on B1 on the first
sheet) then hit enter

(I think this is the way it works) If this does not, dont group the sheets of
the 1 book, enter the =sum( and then go to the other workbook, hold shift key
down, click on B1 on 1st sheet, click b1 on last sheet) hit enter




: Hello
:
: I would have thought this to be easy but it has be stumped!
:
: I have a workbook with 100 sheets (names sheet1 through 100) and I need to
: retrieve the value (either text or number) from cell B2 in each sheet into a
: new workbook.
:
: I have tried copy / pasting the formula but it always refers to Sheet1, I
: cannot seem to make the Sheet number increase?
:
: I hope this makes sense and someone can help?
:
: Cheers,
:
: Carl.
:
:

 

[Carl Jarvis]

Thanks, but that doesnt seem to do what I am looking for (Not all of the
values in B2 will be numbers, some will be words and I don't want to sum
them, I want to list them in a seperate workbook).

For example, in Book 1 I have 3 sheets (Sheet1 to Sheet3) with the letters
A, B and C in cells B2 (Sheet1 B2 = A, Sheet2 B2 = b etc)

In Book 2, starting in cell A1 I want to know what is entered in book1,
sheet1 B2 (which is A), in B1 I want to know what is entered in Book1, Sheet
2 B2 (Which is B) etc

So - Book 2 would look like:-

A - as this is Book1 Sheet1 Cell B2
B - as this is Book1 Sheet2 Cell B2
C - as this is Book1 Sheet3 Cell B2
etc etc etc

Many thanks,

Carl.

 

[JulieD]

Hi Carl

if you paste this code into a module sheet in the new workbook and change
the
"C:\My Documents\myoldbook.xls"
to the path & name of the workbook you want to extract the data from and
Workbooks("Book2.xls").Worksheets("Sheet1").Activate
to the workbook and sheet name that you want to extract the data to

this should then do what you want

****
Sub ExtractB2()

Dim i As Integer

Workbooks.Open Filename:= _
"C:\My Documents\myoldbook.xls"
Workbooks("Book2.xls").Worksheets("Sheet1").Activate

i = 1
For i = 1 To 100
ActiveSheet.Range("A" & i & "").Formula = "=[myoldbook.xls]Sheet" &
i & "!B2"
Next
End Sub

***
let us know how you go.

Cheers
JulieD

 

[Carl Jarvis]

Worked a treat!

Thank you.

Carl.

Hi Carl

if you paste this code into a module sheet in the new workbook and change
the
"C:\My Documents\myoldbook.xls"
to the path & name of the workbook you want to extract the data from and
Workbooks("Book2.xls").Worksheets("Sheet1").Activate
to the workbook and sheet name that you want to extract the data to

this should then do what you want

****
Sub ExtractB2()

Dim i As Integer

Workbooks.Open Filename:= _
"C:\My Documents\myoldbook.xls"
Workbooks("Book2.xls").Worksheets("Sheet1").Activate

i = 1
For i = 1 To 100
ActiveSheet.Range("A" & i & "").Formula = "=[myoldbook.xls]Sheet" &
i & "!B2"
Next
End Sub

***
let us know how you go.

Cheers
JulieD

Thanks, but that doesnt seem to do what I am looking for (Not all of the
values in B2 will be numbers, some will be words and I don't want to sum
them, I want to list them in a seperate workbook).

For example, in Book 1 I have 3 sheets (Sheet1 to Sheet3) with the
letters
A, B and C in cells B2 (Sheet1 B2 = A, Sheet2 B2 = b etc)

In Book 2, starting in cell A1 I want to know what is entered in book1,
sheet1 B2 (which is A), in B1 I want to know what is entered in Book1, Sheet
2 B2 (Which is B) etc

So - Book 2 would look like:-

A - as this is Book1 Sheet1 Cell B2
B - as this is Book1 Sheet2 Cell B2
C - as this is Book1 Sheet3 Cell B2
etc etc etc

Many thanks,

Carl.

 

[JulieD]

glad to help

Worked a treat!

Thank you.

Carl.

Hi Carl

if you paste this code into a module sheet in the new workbook and change
the
"C:\My Documents\myoldbook.xls"
to the path & name of the workbook you want to extract the data from and
Workbooks("Book2.xls").Worksheets("Sheet1").Activate
to the workbook and sheet name that you want to extract the data to

this should then do what you want

****
Sub ExtractB2()

Dim i As Integer

Workbooks.Open Filename:= _
"C:\My Documents\myoldbook.xls"
Workbooks("Book2.xls").Worksheets("Sheet1").Activate

i = 1
For i = 1 To 100
ActiveSheet.Range("A" & i & "").Formula = "=[myoldbook.xls]Sheet" &
i & "!B2"
Next
End Sub

***
let us know how you go.

Cheers
JulieD

Thanks, but that doesnt seem to do what I am looking for (Not all of the
values in B2 will be numbers, some will be words and I don't want to sum
them, I want to list them in a seperate workbook).

For example, in Book 1 I have 3 sheets (Sheet1 to Sheet3) with the
letters
A, B and C in cells B2 (Sheet1 B2 = A, Sheet2 B2 = b etc)

In Book 2, starting in cell A1 I want to know what is entered in book1,
sheet1 B2 (which is A), in B1 I want to know what is entered in Book1, Sheet
2 B2 (Which is B) etc

So - Book 2 would look like:-

A - as this is Book1 Sheet1 Cell B2
B - as this is Book1 Sheet2 Cell B2
C - as this is Book1 Sheet3 Cell B2
etc etc etc

Many thanks,

Carl.
In the workbook with the sheets, group all the sheets (holding shift
key
down,
click on tab for sheet 1 and then click on tab for sheet 100)

In new workbook enter =sum( then go to other workbook, click on B1 on the
first
sheet) then hit enter

(I think this is the way it works) If this does not, dont group the
sheets of
the 1 book, enter the =sum( and then go to the other workbook, hold shift
key
down, click on B1 on 1st sheet, click b1 on last sheet) hit enter




: Hello
:
: I would have thought this to be easy but it has be stumped!
:
: I have a workbook with 100 sheets (names sheet1 through 100) and I need
to
: retrieve the value (either text or number) from cell B2 in each sheet
into a
: new workbook.
:
: I have tried copy / pasting the formula but it always refers to Sheet1,
I
: cannot seem to make the Sheet number increase?
:
: I hope this makes sense and someone can help?
:
: Cheers,
:
: Carl.
:
:

 

[Trevor]

Carl,

The formula ='[WorkbookName.xls]Sheet1'!$B$2 will give you the value you're
looking for.

If you want the value in column 1 (which is column A) to be the value from
Sheet1, and the value in column 2 (column B) to be the value from Sheet2,
then you can place the following formula in cell A1
=INDIRECT("'[WorkbookName.xls]Sheet"&COLUMN()&"'!$B$2")
and copy it across 100 rows an you'll have your answer.

Also, the values on these cells will stay up to date with any changes you
make on the 100-sheet workbook, and you don't have to re-run the macro each
time you make a change.

Trevor