|
grab cell text from multi-tab workbook, show text in another workb
I have a workbook ("A") with 81 tabs. I am looking to grab the text in cell
A50 in each of these tabs and have it "posted" on another workbook ("B") in a cell corresponding to that tab's row. So if I have text such as "no exception noted" on tab 1A-1, cell A50, (from Workbook A), I would like to have that text copied on workbook B in a cell from a column named "summary of results" and in the row that corresponds to the tab named 1A-1. I know how to do this part for a single tab but because there are 81 tabs a way to do this for every tab in a single formula (each tab has obviously a different tab name)? Workbook B tab ref summary of results 1a-1 no exception noted 1b-1 document not signed 1b-2 1c-1 The summary of results text comes from cell A50 in each of the tabs in workbook A. Hope I explain this clearly and someone out there can help. Thanks, pfa |
grab cell text from multi-tab workbook, show text in another workb
I am not sure I understand you correct
Start with this macro that create the links for you to a cell in each sheet http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... I have a workbook ("A") with 81 tabs. I am looking to grab the text in cell A50 in each of these tabs and have it "posted" on another workbook ("B") in a cell corresponding to that tab's row. So if I have text such as "no exception noted" on tab 1A-1, cell A50, (from Workbook A), I would like to have that text copied on workbook B in a cell from a column named "summary of results" and in the row that corresponds to the tab named 1A-1. I know how to do this part for a single tab but because there are 81 tabs a way to do this for every tab in a single formula (each tab has obviously a different tab name)? Workbook B tab ref summary of results 1a-1 no exception noted 1b-1 document not signed 1b-2 1c-1 The summary of results text comes from cell A50 in each of the tabs in workbook A. Hope I explain this clearly and someone out there can help. Thanks, pfa |
grab cell text from multi-tab workbook, show text in another w
I will attempt to clarify. To be specific, I have a workbook named "Summary
Master". In cell O7 of this workbook I have the following formula: ='[DPP Q2 2007 Leadsheets.xls]1A-1'!$A$50. (The DPP Q2 2007 Leadsheets workbook is comprised of 81 tabs, named 1A-1, 1B-1, 1B-2, etc.) I would like to have the text typed in A50 populate cell O7 of the Summary Master workbook every time someone adds tezt to that cell in every one of the 81 tabs on the DPP Q2 workbook. Rather than copy the formula to the next cell (O8), editing the formula above to call up the next tab reference name (i.e., changing the tab name to 1B-1 from 1A-1, for example), my question is if there is an easier way to do this? It just seems a lot of work to edit the formula 81 times...plus there are several other workbooks that I need to link to this summary workbook using the very same concept. I hope I helped clarify this problem for you. "Ron de Bruin" wrote: I am not sure I understand you correct Start with this macro that create the links for you to a cell in each sheet http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... I have a workbook ("A") with 81 tabs. I am looking to grab the text in cell A50 in each of these tabs and have it "posted" on another workbook ("B") in a cell corresponding to that tab's row. So if I have text such as "no exception noted" on tab 1A-1, cell A50, (from Workbook A), I would like to have that text copied on workbook B in a cell from a column named "summary of results" and in the row that corresponds to the tab named 1A-1. I know how to do this part for a single tab but because there are 81 tabs a way to do this for every tab in a single formula (each tab has obviously a different tab name)? Workbook B tab ref summary of results 1a-1 no exception noted 1b-1 document not signed 1b-2 1c-1 The summary of results text comes from cell A50 in each of the tabs in workbook A. Hope I explain this clearly and someone out there can help. Thanks, pfa |
grab cell text from multi-tab workbook, show text in another w
Hi pfa
I will make a example for you tomorrow after work Bed time now for me -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... I will attempt to clarify. To be specific, I have a workbook named "Summary Master". In cell O7 of this workbook I have the following formula: ='[DPP Q2 2007 Leadsheets.xls]1A-1'!$A$50. (The DPP Q2 2007 Leadsheets workbook is comprised of 81 tabs, named 1A-1, 1B-1, 1B-2, etc.) I would like to have the text typed in A50 populate cell O7 of the Summary Master workbook every time someone adds tezt to that cell in every one of the 81 tabs on the DPP Q2 workbook. Rather than copy the formula to the next cell (O8), editing the formula above to call up the next tab reference name (i.e., changing the tab name to 1B-1 from 1A-1, for example), my question is if there is an easier way to do this? It just seems a lot of work to edit the formula 81 times...plus there are several other workbooks that I need to link to this summary workbook using the very same concept. I hope I helped clarify this problem for you. "Ron de Bruin" wrote: I am not sure I understand you correct Start with this macro that create the links for you to a cell in each sheet http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... I have a workbook ("A") with 81 tabs. I am looking to grab the text in cell A50 in each of these tabs and have it "posted" on another workbook ("B") in a cell corresponding to that tab's row. So if I have text such as "no exception noted" on tab 1A-1, cell A50, (from Workbook A), I would like to have that text copied on workbook B in a cell from a column named "summary of results" and in the row that corresponds to the tab named 1A-1. I know how to do this part for a single tab but because there are 81 tabs a way to do this for every tab in a single formula (each tab has obviously a different tab name)? Workbook B tab ref summary of results 1a-1 no exception noted 1b-1 document not signed 1b-2 1c-1 The summary of results text comes from cell A50 in each of the tabs in workbook A. Hope I explain this clearly and someone out there can help. Thanks, pfa |
grab cell text from multi-tab workbook, show text in another w
One way
You can copy this macro in a module in this workbook DPP Q2 2007 Leadsheets.xls After you run it you have a worksheet named "Summary-Sheet" with your 82 formulas Copy the 82 formula cells Open Summary Master.xls Paste the cells (it will add the workbook name now to the formula) Save the file Delete the "Summary-Sheet" in DPP Q2 2007 Leadsheets.xls Sub Summary_All_Worksheets_With_Formulas() Dim Sh As Worksheet Dim Newsh As Worksheet Dim myCell As Range Dim ColNum As Integer Dim RwNum As Long Dim Basebook As Workbook With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Delete the sheet "Summary-Sheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("Summary-Sheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "Summary-Sheet" Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add Newsh.Name = "Summary-Sheet" 'The links to the first sheet will start in row 2 RwNum = 1 For Each Sh In Basebook.Worksheets If Sh.Name < Newsh.Name And Sh.Visible Then ColNum = 1 RwNum = RwNum + 1 'Copy the sheet name in the A column Newsh.Cells(RwNum, 1).Value = Sh.Name For Each myCell In Sh.Range("A50") '<--Change the range ColNum = ColNum + 1 Newsh.Cells(RwNum, ColNum).Formula = _ "='" & Sh.Name & "'!" & myCell.Address(True, True) Next myCell End If Next Sh Newsh.UsedRange.Columns.AutoFit With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi pfa I will make a example for you tomorrow after work Bed time now for me -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... I will attempt to clarify. To be specific, I have a workbook named "Summary Master". In cell O7 of this workbook I have the following formula: ='[DPP Q2 2007 Leadsheets.xls]1A-1'!$A$50. (The DPP Q2 2007 Leadsheets workbook is comprised of 81 tabs, named 1A-1, 1B-1, 1B-2, etc.) I would like to have the text typed in A50 populate cell O7 of the Summary Master workbook every time someone adds tezt to that cell in every one of the 81 tabs on the DPP Q2 workbook. Rather than copy the formula to the next cell (O8), editing the formula above to call up the next tab reference name (i.e., changing the tab name to 1B-1 from 1A-1, for example), my question is if there is an easier way to do this? It just seems a lot of work to edit the formula 81 times...plus there are several other workbooks that I need to link to this summary workbook using the very same concept. I hope I helped clarify this problem for you. "Ron de Bruin" wrote: I am not sure I understand you correct Start with this macro that create the links for you to a cell in each sheet http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... I have a workbook ("A") with 81 tabs. I am looking to grab the text in cell A50 in each of these tabs and have it "posted" on another workbook ("B") in a cell corresponding to that tab's row. So if I have text such as "no exception noted" on tab 1A-1, cell A50, (from Workbook A), I would like to have that text copied on workbook B in a cell from a column named "summary of results" and in the row that corresponds to the tab named 1A-1. I know how to do this part for a single tab but because there are 81 tabs a way to do this for every tab in a single formula (each tab has obviously a different tab name)? Workbook B tab ref summary of results 1a-1 no exception noted 1b-1 document not signed 1b-2 1c-1 The summary of results text comes from cell A50 in each of the tabs in workbook A. Hope I explain this clearly and someone out there can help. Thanks, pfa |
grab cell text from multi-tab workbook, show text in another w
Ron, It worked to perfection. Thanks for being so helpful! I will add a caveat to this problem tomorrow that may or may not be possible to address. For now, many thanks! "Ron de Bruin" wrote: One way You can copy this macro in a module in this workbook DPP Q2 2007 Leadsheets.xls After you run it you have a worksheet named "Summary-Sheet" with your 82 formulas Copy the 82 formula cells Open Summary Master.xls Paste the cells (it will add the workbook name now to the formula) Save the file Delete the "Summary-Sheet" in DPP Q2 2007 Leadsheets.xls Sub Summary_All_Worksheets_With_Formulas() Dim Sh As Worksheet Dim Newsh As Worksheet Dim myCell As Range Dim ColNum As Integer Dim RwNum As Long Dim Basebook As Workbook With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Delete the sheet "Summary-Sheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("Summary-Sheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "Summary-Sheet" Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add Newsh.Name = "Summary-Sheet" 'The links to the first sheet will start in row 2 RwNum = 1 For Each Sh In Basebook.Worksheets If Sh.Name < Newsh.Name And Sh.Visible Then ColNum = 1 RwNum = RwNum + 1 'Copy the sheet name in the A column Newsh.Cells(RwNum, 1).Value = Sh.Name For Each myCell In Sh.Range("A50") '<--Change the range ColNum = ColNum + 1 Newsh.Cells(RwNum, ColNum).Formula = _ "='" & Sh.Name & "'!" & myCell.Address(True, True) Next myCell End If Next Sh Newsh.UsedRange.Columns.AutoFit With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi pfa I will make a example for you tomorrow after work Bed time now for me -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... I will attempt to clarify. To be specific, I have a workbook named "Summary Master". In cell O7 of this workbook I have the following formula: ='[DPP Q2 2007 Leadsheets.xls]1A-1'!$A$50. (The DPP Q2 2007 Leadsheets workbook is comprised of 81 tabs, named 1A-1, 1B-1, 1B-2, etc.) I would like to have the text typed in A50 populate cell O7 of the Summary Master workbook every time someone adds tezt to that cell in every one of the 81 tabs on the DPP Q2 workbook. Rather than copy the formula to the next cell (O8), editing the formula above to call up the next tab reference name (i.e., changing the tab name to 1B-1 from 1A-1, for example), my question is if there is an easier way to do this? It just seems a lot of work to edit the formula 81 times...plus there are several other workbooks that I need to link to this summary workbook using the very same concept. I hope I helped clarify this problem for you. "Ron de Bruin" wrote: I am not sure I understand you correct Start with this macro that create the links for you to a cell in each sheet http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... I have a workbook ("A") with 81 tabs. I am looking to grab the text in cell A50 in each of these tabs and have it "posted" on another workbook ("B") in a cell corresponding to that tab's row. So if I have text such as "no exception noted" on tab 1A-1, cell A50, (from Workbook A), I would like to have that text copied on workbook B in a cell from a column named "summary of results" and in the row that corresponds to the tab named 1A-1. I know how to do this part for a single tab but because there are 81 tabs a way to do this for every tab in a single formula (each tab has obviously a different tab name)? Workbook B tab ref summary of results 1a-1 no exception noted 1b-1 document not signed 1b-2 1c-1 The summary of results text comes from cell A50 in each of the tabs in workbook A. Hope I explain this clearly and someone out there can help. Thanks, pfa |
grab cell text from multi-tab workbook, show text in another w
Ron,
is there an alternative solution in the event "summary master" has more than the 82 rows but DPP Q2 only has those 82 tabs I talked about? In other words, is there a way to make sure that the copy function can drop the right formula (i.e., the appropriate tab reference name) in the proper corresponding cell in "summary master"? If there is a way, great. If not, I will just manually adjust in the summary master. Thanks! "pfa" wrote: Ron, It worked to perfection. Thanks for being so helpful! I will add a caveat to this problem tomorrow that may or may not be possible to address. For now, many thanks! "Ron de Bruin" wrote: One way You can copy this macro in a module in this workbook DPP Q2 2007 Leadsheets.xls After you run it you have a worksheet named "Summary-Sheet" with your 82 formulas Copy the 82 formula cells Open Summary Master.xls Paste the cells (it will add the workbook name now to the formula) Save the file Delete the "Summary-Sheet" in DPP Q2 2007 Leadsheets.xls Sub Summary_All_Worksheets_With_Formulas() Dim Sh As Worksheet Dim Newsh As Worksheet Dim myCell As Range Dim ColNum As Integer Dim RwNum As Long Dim Basebook As Workbook With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Delete the sheet "Summary-Sheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("Summary-Sheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "Summary-Sheet" Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add Newsh.Name = "Summary-Sheet" 'The links to the first sheet will start in row 2 RwNum = 1 For Each Sh In Basebook.Worksheets If Sh.Name < Newsh.Name And Sh.Visible Then ColNum = 1 RwNum = RwNum + 1 'Copy the sheet name in the A column Newsh.Cells(RwNum, 1).Value = Sh.Name For Each myCell In Sh.Range("A50") '<--Change the range ColNum = ColNum + 1 Newsh.Cells(RwNum, ColNum).Formula = _ "='" & Sh.Name & "'!" & myCell.Address(True, True) Next myCell End If Next Sh Newsh.UsedRange.Columns.AutoFit With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi pfa I will make a example for you tomorrow after work Bed time now for me -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... I will attempt to clarify. To be specific, I have a workbook named "Summary Master". In cell O7 of this workbook I have the following formula: ='[DPP Q2 2007 Leadsheets.xls]1A-1'!$A$50. (The DPP Q2 2007 Leadsheets workbook is comprised of 81 tabs, named 1A-1, 1B-1, 1B-2, etc.) I would like to have the text typed in A50 populate cell O7 of the Summary Master workbook every time someone adds tezt to that cell in every one of the 81 tabs on the DPP Q2 workbook. Rather than copy the formula to the next cell (O8), editing the formula above to call up the next tab reference name (i.e., changing the tab name to 1B-1 from 1A-1, for example), my question is if there is an easier way to do this? It just seems a lot of work to edit the formula 81 times...plus there are several other workbooks that I need to link to this summary workbook using the very same concept. I hope I helped clarify this problem for you. "Ron de Bruin" wrote: I am not sure I understand you correct Start with this macro that create the links for you to a cell in each sheet http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... I have a workbook ("A") with 81 tabs. I am looking to grab the text in cell A50 in each of these tabs and have it "posted" on another workbook ("B") in a cell corresponding to that tab's row. So if I have text such as "no exception noted" on tab 1A-1, cell A50, (from Workbook A), I would like to have that text copied on workbook B in a cell from a column named "summary of results" and in the row that corresponds to the tab named 1A-1. I know how to do this part for a single tab but because there are 81 tabs a way to do this for every tab in a single formula (each tab has obviously a different tab name)? Workbook B tab ref summary of results 1a-1 no exception noted 1b-1 document not signed 1b-2 1c-1 The summary of results text comes from cell A50 in each of the tabs in workbook A. Hope I explain this clearly and someone out there can help. Thanks, pfa |
grab cell text from multi-tab workbook, show text in another w
Hi pfa
I am not sure what you exactly want Can you give more details -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... Ron, is there an alternative solution in the event "summary master" has more than the 82 rows but DPP Q2 only has those 82 tabs I talked about? In other words, is there a way to make sure that the copy function can drop the right formula (i.e., the appropriate tab reference name) in the proper corresponding cell in "summary master"? If there is a way, great. If not, I will just manually adjust in the summary master. Thanks! "pfa" wrote: Ron, It worked to perfection. Thanks for being so helpful! I will add a caveat to this problem tomorrow that may or may not be possible to address. For now, many thanks! "Ron de Bruin" wrote: One way You can copy this macro in a module in this workbook DPP Q2 2007 Leadsheets.xls After you run it you have a worksheet named "Summary-Sheet" with your 82 formulas Copy the 82 formula cells Open Summary Master.xls Paste the cells (it will add the workbook name now to the formula) Save the file Delete the "Summary-Sheet" in DPP Q2 2007 Leadsheets.xls Sub Summary_All_Worksheets_With_Formulas() Dim Sh As Worksheet Dim Newsh As Worksheet Dim myCell As Range Dim ColNum As Integer Dim RwNum As Long Dim Basebook As Workbook With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Delete the sheet "Summary-Sheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("Summary-Sheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "Summary-Sheet" Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add Newsh.Name = "Summary-Sheet" 'The links to the first sheet will start in row 2 RwNum = 1 For Each Sh In Basebook.Worksheets If Sh.Name < Newsh.Name And Sh.Visible Then ColNum = 1 RwNum = RwNum + 1 'Copy the sheet name in the A column Newsh.Cells(RwNum, 1).Value = Sh.Name For Each myCell In Sh.Range("A50") '<--Change the range ColNum = ColNum + 1 Newsh.Cells(RwNum, ColNum).Formula = _ "='" & Sh.Name & "'!" & myCell.Address(True, True) Next myCell End If Next Sh Newsh.UsedRange.Columns.AutoFit With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi pfa I will make a example for you tomorrow after work Bed time now for me -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... I will attempt to clarify. To be specific, I have a workbook named "Summary Master". In cell O7 of this workbook I have the following formula: ='[DPP Q2 2007 Leadsheets.xls]1A-1'!$A$50. (The DPP Q2 2007 Leadsheets workbook is comprised of 81 tabs, named 1A-1, 1B-1, 1B-2, etc.) I would like to have the text typed in A50 populate cell O7 of the Summary Master workbook every time someone adds tezt to that cell in every one of the 81 tabs on the DPP Q2 workbook. Rather than copy the formula to the next cell (O8), editing the formula above to call up the next tab reference name (i.e., changing the tab name to 1B-1 from 1A-1, for example), my question is if there is an easier way to do this? It just seems a lot of work to edit the formula 81 times...plus there are several other workbooks that I need to link to this summary workbook using the very same concept. I hope I helped clarify this problem for you. "Ron de Bruin" wrote: I am not sure I understand you correct Start with this macro that create the links for you to a cell in each sheet http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... I have a workbook ("A") with 81 tabs. I am looking to grab the text in cell A50 in each of these tabs and have it "posted" on another workbook ("B") in a cell corresponding to that tab's row. So if I have text such as "no exception noted" on tab 1A-1, cell A50, (from Workbook A), I would like to have that text copied on workbook B in a cell from a column named "summary of results" and in the row that corresponds to the tab named 1A-1. I know how to do this part for a single tab but because there are 81 tabs a way to do this for every tab in a single formula (each tab has obviously a different tab name)? Workbook B tab ref summary of results 1a-1 no exception noted 1b-1 document not signed 1b-2 1c-1 The summary of results text comes from cell A50 in each of the tabs in workbook A. Hope I explain this clearly and someone out there can help. Thanks, pfa |
grab cell text from multi-tab workbook, show text in another w
Hi pfa
I am not sure what you exactly want Can you give more details -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... Ron, is there an alternative solution in the event "summary master" has more than the 82 rows but DPP Q2 only has those 82 tabs I talked about? In other words, is there a way to make sure that the copy function can drop the right formula (i.e., the appropriate tab reference name) in the proper corresponding cell in "summary master"? If there is a way, great. If not, I will just manually adjust in the summary master. Thanks! "pfa" wrote: Ron, It worked to perfection. Thanks for being so helpful! I will add a caveat to this problem tomorrow that may or may not be possible to address. For now, many thanks! "Ron de Bruin" wrote: One way You can copy this macro in a module in this workbook DPP Q2 2007 Leadsheets.xls After you run it you have a worksheet named "Summary-Sheet" with your 82 formulas Copy the 82 formula cells Open Summary Master.xls Paste the cells (it will add the workbook name now to the formula) Save the file Delete the "Summary-Sheet" in DPP Q2 2007 Leadsheets.xls Sub Summary_All_Worksheets_With_Formulas() Dim Sh As Worksheet Dim Newsh As Worksheet Dim myCell As Range Dim ColNum As Integer Dim RwNum As Long Dim Basebook As Workbook With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Delete the sheet "Summary-Sheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("Summary-Sheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "Summary-Sheet" Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add Newsh.Name = "Summary-Sheet" 'The links to the first sheet will start in row 2 RwNum = 1 For Each Sh In Basebook.Worksheets If Sh.Name < Newsh.Name And Sh.Visible Then ColNum = 1 RwNum = RwNum + 1 'Copy the sheet name in the A column Newsh.Cells(RwNum, 1).Value = Sh.Name For Each myCell In Sh.Range("A50") '<--Change the range ColNum = ColNum + 1 Newsh.Cells(RwNum, ColNum).Formula = _ "='" & Sh.Name & "'!" & myCell.Address(True, True) Next myCell End If Next Sh Newsh.UsedRange.Columns.AutoFit With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi pfa I will make a example for you tomorrow after work Bed time now for me -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... I will attempt to clarify. To be specific, I have a workbook named "Summary Master". In cell O7 of this workbook I have the following formula: ='[DPP Q2 2007 Leadsheets.xls]1A-1'!$A$50. (The DPP Q2 2007 Leadsheets workbook is comprised of 81 tabs, named 1A-1, 1B-1, 1B-2, etc.) I would like to have the text typed in A50 populate cell O7 of the Summary Master workbook every time someone adds tezt to that cell in every one of the 81 tabs on the DPP Q2 workbook. Rather than copy the formula to the next cell (O8), editing the formula above to call up the next tab reference name (i.e., changing the tab name to 1B-1 from 1A-1, for example), my question is if there is an easier way to do this? It just seems a lot of work to edit the formula 81 times...plus there are several other workbooks that I need to link to this summary workbook using the very same concept. I hope I helped clarify this problem for you. "Ron de Bruin" wrote: I am not sure I understand you correct Start with this macro that create the links for you to a cell in each sheet http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... I have a workbook ("A") with 81 tabs. I am looking to grab the text in cell A50 in each of these tabs and have it "posted" on another workbook ("B") in a cell corresponding to that tab's row. So if I have text such as "no exception noted" on tab 1A-1, cell A50, (from Workbook A), I would like to have that text copied on workbook B in a cell from a column named "summary of results" and in the row that corresponds to the tab named 1A-1. I know how to do this part for a single tab but because there are 81 tabs a way to do this for every tab in a single formula (each tab has obviously a different tab name)? Workbook B tab ref summary of results 1a-1 no exception noted 1b-1 document not signed 1b-2 1c-1 The summary of results text comes from cell A50 in each of the tabs in workbook A. Hope I explain this clearly and someone out there can help. Thanks, pfa |
grab cell text from multi-tab workbook, show text in another w
Ron,
I will try to explain it a little better. The Summary Master workbook is actually comprised of 118 rows. Each row, if you remember contains a cell that should be populated with text from a cell residing in some other workbook. If you recall the other workbook, DPP Q2, the one for which you created that macro - is comprised of 82 tabs. So not all rows in Summary Master will be populated with the formula you created for me. In effect, there are no other workbooks that I want to link to Summary Master that have all 118 tabs. So my question is if it is possible to modify the macro to have it populate only the applicable cells - those matching the same reference number - without my having to go into Summary Master to copy and paste, making sure the correct formula is in the correct cell? So the example would be as follows: In Summary Master I may have in A1 1A-1 A2 1B-1 A3 1B-2 A4 1C-1 But in "DPP Q2" I may just have tabs named 1A-1, 1B-1 and 1C-1. Now let's assume "DPP Q2" does not have a 1B-2 named tab. With your macro I get the formulas for 1A-1, 1B-1 and 1C-1. Then I have to go into Summary Master and insert to shift one cell down so I have the formula for 1C-1 where it belongs. This is fine, if there's no other solution. It is far better than having to modify the formula row by row, times the number of workbooks that will eventually be linked to Summary Master, as I was previously faced with (before macro) The idea would be to have some way for the macro to be modified to match the reference (the tab name from DPP Q2) in Summary Master and place the formula where it should be. I am guessing this extremely difficult to do, or perhaps even impossible, but I just wanted to run by you to get your take on it. I really appreciate your assistance and patience with this. Thank you very much! "Ron de Bruin" wrote: Hi pfa I am not sure what you exactly want Can you give more details -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... Ron, is there an alternative solution in the event "summary master" has more than the 82 rows but DPP Q2 only has those 82 tabs I talked about? In other words, is there a way to make sure that the copy function can drop the right formula (i.e., the appropriate tab reference name) in the proper corresponding cell in "summary master"? If there is a way, great. If not, I will just manually adjust in the summary master. Thanks! "pfa" wrote: Ron, It worked to perfection. Thanks for being so helpful! I will add a caveat to this problem tomorrow that may or may not be possible to address. For now, many thanks! "Ron de Bruin" wrote: One way You can copy this macro in a module in this workbook DPP Q2 2007 Leadsheets.xls After you run it you have a worksheet named "Summary-Sheet" with your 82 formulas Copy the 82 formula cells Open Summary Master.xls Paste the cells (it will add the workbook name now to the formula) Save the file Delete the "Summary-Sheet" in DPP Q2 2007 Leadsheets.xls Sub Summary_All_Worksheets_With_Formulas() Dim Sh As Worksheet Dim Newsh As Worksheet Dim myCell As Range Dim ColNum As Integer Dim RwNum As Long Dim Basebook As Workbook With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Delete the sheet "Summary-Sheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("Summary-Sheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "Summary-Sheet" Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add Newsh.Name = "Summary-Sheet" 'The links to the first sheet will start in row 2 RwNum = 1 For Each Sh In Basebook.Worksheets If Sh.Name < Newsh.Name And Sh.Visible Then ColNum = 1 RwNum = RwNum + 1 'Copy the sheet name in the A column Newsh.Cells(RwNum, 1).Value = Sh.Name For Each myCell In Sh.Range("A50") '<--Change the range ColNum = ColNum + 1 Newsh.Cells(RwNum, ColNum).Formula = _ "='" & Sh.Name & "'!" & myCell.Address(True, True) Next myCell End If Next Sh Newsh.UsedRange.Columns.AutoFit With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi pfa I will make a example for you tomorrow after work Bed time now for me -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... I will attempt to clarify. To be specific, I have a workbook named "Summary Master". In cell O7 of this workbook I have the following formula: ='[DPP Q2 2007 Leadsheets.xls]1A-1'!$A$50. (The DPP Q2 2007 Leadsheets workbook is comprised of 81 tabs, named 1A-1, 1B-1, 1B-2, etc.) I would like to have the text typed in A50 populate cell O7 of the Summary Master workbook every time someone adds tezt to that cell in every one of the 81 tabs on the DPP Q2 workbook. Rather than copy the formula to the next cell (O8), editing the formula above to call up the next tab reference name (i.e., changing the tab name to 1B-1 from 1A-1, for example), my question is if there is an easier way to do this? It just seems a lot of work to edit the formula 81 times...plus there are several other workbooks that I need to link to this summary workbook using the very same concept. I hope I helped clarify this problem for you. "Ron de Bruin" wrote: I am not sure I understand you correct Start with this macro that create the links for you to a cell in each sheet http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... I have a workbook ("A") with 81 tabs. I am looking to grab the text in cell A50 in each of these tabs and have it "posted" on another workbook ("B") in a cell corresponding to that tab's row. So if I have text such as "no exception noted" on tab 1A-1, cell A50, (from Workbook A), I would like to have that text copied on workbook B in a cell from a column named "summary of results" and in the row that corresponds to the tab named 1A-1. I know how to do this part for a single tab but because there are 81 tabs a way to do this for every tab in a single formula (each tab has obviously a different tab name)? Workbook B tab ref summary of results 1a-1 no exception noted 1b-1 document not signed 1b-2 1c-1 The summary of results text comes from cell A50 in each of the tabs in workbook A. Hope I explain this clearly and someone out there can help. Thanks, pfa |
grab cell text from multi-tab workbook, show text in another w
I see.
I think we better use another macro for this this. We run that with both workbooks open and add the formulas in the correct place. I make one for you tomorrow after work (sorry no time now) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... Ron, I will try to explain it a little better. The Summary Master workbook is actually comprised of 118 rows. Each row, if you remember contains a cell that should be populated with text from a cell residing in some other workbook. If you recall the other workbook, DPP Q2, the one for which you created that macro - is comprised of 82 tabs. So not all rows in Summary Master will be populated with the formula you created for me. In effect, there are no other workbooks that I want to link to Summary Master that have all 118 tabs. So my question is if it is possible to modify the macro to have it populate only the applicable cells - those matching the same reference number - without my having to go into Summary Master to copy and paste, making sure the correct formula is in the correct cell? So the example would be as follows: In Summary Master I may have in A1 1A-1 A2 1B-1 A3 1B-2 A4 1C-1 But in "DPP Q2" I may just have tabs named 1A-1, 1B-1 and 1C-1. Now let's assume "DPP Q2" does not have a 1B-2 named tab. With your macro I get the formulas for 1A-1, 1B-1 and 1C-1. Then I have to go into Summary Master and insert to shift one cell down so I have the formula for 1C-1 where it belongs. This is fine, if there's no other solution. It is far better than having to modify the formula row by row, times the number of workbooks that will eventually be linked to Summary Master, as I was previously faced with (before macro) The idea would be to have some way for the macro to be modified to match the reference (the tab name from DPP Q2) in Summary Master and place the formula where it should be. I am guessing this extremely difficult to do, or perhaps even impossible, but I just wanted to run by you to get your take on it. I really appreciate your assistance and patience with this. Thank you very much! "Ron de Bruin" wrote: Hi pfa I am not sure what you exactly want Can you give more details -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... Ron, is there an alternative solution in the event "summary master" has more than the 82 rows but DPP Q2 only has those 82 tabs I talked about? In other words, is there a way to make sure that the copy function can drop the right formula (i.e., the appropriate tab reference name) in the proper corresponding cell in "summary master"? If there is a way, great. If not, I will just manually adjust in the summary master. Thanks! "pfa" wrote: Ron, It worked to perfection. Thanks for being so helpful! I will add a caveat to this problem tomorrow that may or may not be possible to address. For now, many thanks! "Ron de Bruin" wrote: One way You can copy this macro in a module in this workbook DPP Q2 2007 Leadsheets.xls After you run it you have a worksheet named "Summary-Sheet" with your 82 formulas Copy the 82 formula cells Open Summary Master.xls Paste the cells (it will add the workbook name now to the formula) Save the file Delete the "Summary-Sheet" in DPP Q2 2007 Leadsheets.xls Sub Summary_All_Worksheets_With_Formulas() Dim Sh As Worksheet Dim Newsh As Worksheet Dim myCell As Range Dim ColNum As Integer Dim RwNum As Long Dim Basebook As Workbook With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Delete the sheet "Summary-Sheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("Summary-Sheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "Summary-Sheet" Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add Newsh.Name = "Summary-Sheet" 'The links to the first sheet will start in row 2 RwNum = 1 For Each Sh In Basebook.Worksheets If Sh.Name < Newsh.Name And Sh.Visible Then ColNum = 1 RwNum = RwNum + 1 'Copy the sheet name in the A column Newsh.Cells(RwNum, 1).Value = Sh.Name For Each myCell In Sh.Range("A50") '<--Change the range ColNum = ColNum + 1 Newsh.Cells(RwNum, ColNum).Formula = _ "='" & Sh.Name & "'!" & myCell.Address(True, True) Next myCell End If Next Sh Newsh.UsedRange.Columns.AutoFit With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi pfa I will make a example for you tomorrow after work Bed time now for me -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... I will attempt to clarify. To be specific, I have a workbook named "Summary Master". In cell O7 of this workbook I have the following formula: ='[DPP Q2 2007 Leadsheets.xls]1A-1'!$A$50. (The DPP Q2 2007 Leadsheets workbook is comprised of 81 tabs, named 1A-1, 1B-1, 1B-2, etc.) I would like to have the text typed in A50 populate cell O7 of the Summary Master workbook every time someone adds tezt to that cell in every one of the 81 tabs on the DPP Q2 workbook. Rather than copy the formula to the next cell (O8), editing the formula above to call up the next tab reference name (i.e., changing the tab name to 1B-1 from 1A-1, for example), my question is if there is an easier way to do this? It just seems a lot of work to edit the formula 81 times...plus there are several other workbooks that I need to link to this summary workbook using the very same concept. I hope I helped clarify this problem for you. "Ron de Bruin" wrote: I am not sure I understand you correct Start with this macro that create the links for you to a cell in each sheet http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... I have a workbook ("A") with 81 tabs. I am looking to grab the text in cell A50 in each of these tabs and have it "posted" on another workbook ("B") in a cell corresponding to that tab's row. So if I have text such as "no exception noted" on tab 1A-1, cell A50, (from Workbook A), I would like to have that text copied on workbook B in a cell from a column named "summary of results" and in the row that corresponds to the tab named 1A-1. I know how to do this part for a single tab but because there are 81 tabs a way to do this for every tab in a single formula (each tab has obviously a different tab name)? Workbook B tab ref summary of results 1a-1 no exception noted 1b-1 document not signed 1b-2 1c-1 The summary of results text comes from cell A50 in each of the tabs in workbook A. Hope I explain this clearly and someone out there can help. Thanks, pfa |
grab cell text from multi-tab workbook, show text in another w
Ron, Thanks again for your assistance. You have been extremely helpful. I look forward for your solution tomorrow. Many thanks! |
grab cell text from multi-tab workbook, show text in another w
Ok, try this test macro
Open Excel and only open the Summary Master workbook Copy the macro in the Summary Master file and be sure that sheet where you want to have the links is active When you run the macro you can browse to the file with the 82 tabs and the code will open the file. It will loop through all sheets and of the sheet name exist in column A of the Summary Master workbook in will add the formula next to it in column B. When the code is ready it will close the file with 82 tabs. Let me know if this is what you want ? Sub Test() Dim FileNameXls As Variant Dim DestWks As Worksheet Dim Rng As Range Dim FinalSlash As Long Dim PathStr As String Dim JustFileName As String Dim JustFolder As String Dim mybook As Workbook Dim sh As Worksheet 'Select the file with GetOpenFilename FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", _ MultiSelect:=False) If FileNameXls = False Then 'do nothing Else With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With Set DestWks = ActiveSheet Set mybook = Workbooks.Open(FileNameXls) FinalSlash = InStrRev(FileNameXls, "\") JustFileName = Mid(FileNameXls, FinalSlash + 1) JustFolder = Left(FileNameXls, FinalSlash - 1) For Each sh In mybook.Worksheets With DestWks.Range("A:A") Set Rng = .Find(What:=sh.Name, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not Rng Is Nothing Then 'build the formula string JustFileName = WorksheetFunction.Substitute(JustFileName, "'", "''") PathStr = "'" & JustFolder & "\[" & JustFileName & "]" & sh.Name & "'!" Rng.Offset(0, 1).Formula = _ "=" & PathStr & Range("A50").Address Else 'do nothing End If End With Next sh mybook.Close False MsgBox "The macro is ready, check the result" With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... Ron, Thanks again for your assistance. You have been extremely helpful. I look forward for your solution tomorrow. Many thanks! |
grab cell text from multi-tab workbook, show text in another w
Ron, Seems to do exactly what I requested. However, I neglected to tell you the reference numbers in Summary Master are in Column D, and the results for DPP Q2 should appear in Column O. If you let me know what changes in the code are necessary to get this result, I can then use the same code for all the other workbooks that I need to drop the same type information into the Summary Master. Again, many thanks for your patience and help. |
grab cell text from multi-tab workbook, show text in another w
Hi pfa
Firts we change the search column from A to D With DestWks.Range("A:A") Change to With DestWks.Range("D:D") And the we change the offset from 1 to 11 Column O is 11 columns to the right from column D (your seach column) Rng.Offset(0, 1).Formula = _ "=" & PathStr & Range("A50").Address To Rng.Offset(0, 11).Formula = _ "=" & PathStr & Range("A50").Address -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... Ron, Seems to do exactly what I requested. However, I neglected to tell you the reference numbers in Summary Master are in Column D, and the results for DPP Q2 should appear in Column O. If you let me know what changes in the code are necessary to get this result, I can then use the same code for all the other workbooks that I need to drop the same type information into the Summary Master. Again, many thanks for your patience and help. |
grab cell text from multi-tab workbook, show text in another w
Ron,
Many thanks again for the assistance. It works to perfection!! Now I can link the other workbooks as well. Couldn't have done it without your precious help. "Ron de Bruin" wrote: Hi pfa Firts we change the search column from A to D With DestWks.Range("A:A") Change to With DestWks.Range("D:D") And the we change the offset from 1 to 11 Column O is 11 columns to the right from column D (your seach column) Rng.Offset(0, 1).Formula = _ "=" & PathStr & Range("A50").Address To Rng.Offset(0, 11).Formula = _ "=" & PathStr & Range("A50").Address -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... Ron, Seems to do exactly what I requested. However, I neglected to tell you the reference numbers in Summary Master are in Column D, and the results for DPP Q2 should appear in Column O. If you let me know what changes in the code are necessary to get this result, I can then use the same code for all the other workbooks that I need to drop the same type information into the Summary Master. Again, many thanks for your patience and help. |
grab cell text from multi-tab workbook, show text in another w
You are welcome
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... Ron, Many thanks again for the assistance. It works to perfection!! Now I can link the other workbooks as well. Couldn't have done it without your precious help. "Ron de Bruin" wrote: Hi pfa Firts we change the search column from A to D With DestWks.Range("A:A") Change to With DestWks.Range("D:D") And the we change the offset from 1 to 11 Column O is 11 columns to the right from column D (your seach column) Rng.Offset(0, 1).Formula = _ "=" & PathStr & Range("A50").Address To Rng.Offset(0, 11).Formula = _ "=" & PathStr & Range("A50").Address -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pfa" wrote in message ... Ron, Seems to do exactly what I requested. However, I neglected to tell you the reference numbers in Summary Master are in Column D, and the results for DPP Q2 should appear in Column O. If you let me know what changes in the code are necessary to get this result, I can then use the same code for all the other workbooks that I need to drop the same type information into the Summary Master. Again, many thanks for your patience and help. |
| All times are GMT +1. The time now is 06:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com