If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Retrieve the same cell ref from multiple sheets?
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. |
#2
|
|||
|
|||
Retrieve the same cell ref from multiple sheets?
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 "Carl Jarvis" [email protected] wrote in message ... : 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. : : |
#3
|
|||
|
|||
Retrieve the same cell ref from multiple sheets?
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. "Lady Layla" wrote in message ... 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 "Carl Jarvis" [email protected] wrote in message ... : 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. : : |
#4
|
|||
|
|||
Retrieve the same cell ref from multiple sheets?
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").Activa te 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").Activa te 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" [email protected] wrote in message ... 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. "Lady Layla" wrote in message ... 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 "Carl Jarvis" [email protected] wrote in message ... : 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. : : |
#5
|
|||
|
|||
Retrieve the same cell ref from multiple sheets?
Worked a treat!
Thank you. Carl. "JulieD" wrote in message ... 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").Activa te 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").Activa te 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" [email protected] wrote in message ... 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. "Lady Layla" wrote in message ... 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 "Carl Jarvis" [email protected] wrote in message ... : 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. : : |
#6
|
|||
|
|||
Retrieve the same cell ref from multiple sheets?
glad to help
"Carl Jarvis" [email protected] wrote in message ... Worked a treat! Thank you. Carl. "JulieD" wrote in message ... 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").Activa te 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").Activa te 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" [email protected] wrote in message ... 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. "Lady Layla" wrote in message ... 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 "Carl Jarvis" [email protected] wrote in message ... : 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. : : |
#7
|
|||
|
|||
Retrieve the same cell ref from multiple sheets?
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 "Carl Jarvis" [email protected] wrote in message ... 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. "Lady Layla" wrote in message ... 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 "Carl Jarvis" [email protected] wrote in message ... : 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. : : |
Thread Tools | |
Display Modes | |
|
|