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

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

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

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

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

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

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

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

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

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

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

Ron,

Thanks again for your assistance. You have been extremely helpful.
I look forward for your solution tomorrow.

Many thanks!

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!

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.

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.

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.

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.