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