Hello, is this message visible?

[robert morris]

I have posted this question 3 times in the last five days without any
response. Never having had this problem before, I wonder if my post is
visible.

Post;

As I copy and add new worksheets from a "master" worksheet in the same
workbook, I also have seven links to a 'summary" worksheet. Is there a VBA,
or some way to add a blank row to the "summary" worksheet as I add a new
worksheet from the master and have the new row in the "summary" worksheet
auto populate data from linked cells from the newest worksheet as data is
changed?

I would really appreciate any help.

Bob

 

[Bernie Deitrick]

Your post is visible. You need to run a macro, along the lines of this, run when the newly added
sheet is active - change the cell addresses to reflect the links that you require.

Sub MakeLinks()
Dim myR As Long
With Worksheets("Master")
myR = .Cells(Rows.Count, 1).End(xlUp)(2).Row
..Cells(myR, 1).Formula = "='" & ActiveSheet.Name & "'!A1"
..Cells(myR, 2).Formula = "='" & ActiveSheet.Name & "'!B2"
..Cells(myR, 3).Formula = "='" & ActiveSheet.Name & "'!C3"
..Cells(myR, 4).Formula = "='" & ActiveSheet.Name & "'!D4"
..Cells(myR, 5).Formula = "='" & ActiveSheet.Name & "'!E5"
..Cells(myR, 6).Formula = "='" & ActiveSheet.Name & "'!F6"
..Cells(myR, 7).Formula = "='" & ActiveSheet.Name & "'!G7"
End With
End Sub

 

[David Biddulph]

No, this message isn't visible.

As far as previous messages are concerned, the only sign I can see in the
archive is
http://groups.google.co.uk/group/mi...6/ef240999770fed54?lnk=st&q=#ef240999770fed54
in microsoft.public.excel, which probably isn't as widely read as
..excel.misc.

Perhaps the reason why you didn't get an answer was that it wasn't
particularly clear (to me at least) what your problem was. If you are
saying that you want to point a formula at various cells in a new sheet when
a new sheet is added, then perhaps you should be using the INDIRECT function
to provide the links to the new sheet? For example, the formula
=INDIRECT("'Sheet"&ROW(A1)&"'!$M$75") would provide a link to Sheet1!M75,
and if you copy that formula down a column the subsequent rows will link to
Sheet2!M75, Sheet3!M75, etc.

Perhaps you can give examples as to what your formula looks like at present,
and then we can suggest how you might improve it.

 

[robert morris]

Bernie,

Thanks for answering. I thought I was doing something wrong. I am not
having any luck making your code work, my fault I'm sure. I believe you are
on the right track. Some questions; where do I enter the code, in Sheet 1
(results sheet), or sheet 2 (master sheet which contains my criteria)? You
also say to change the cell addresses. Are these the ones A1, B2, C3,...?
Do I change them to my cells i.e., M75, D75, F73, ...? It looks as though
line 4 of your code is designed to add a new Row to the summary sheet (sheet
1), correct? Once I get the code working, do I do a "run macro"?

The summary sheet is also sorted by each of the seven columns. What I'm
doing now is linking each of the seven links to the summary sheet one at a
time. If we can make this work, it will save me countless hours of time as
well making the workbook more accurate.

One other note; the seven lines in your code starting ..Cells(MyR
..........show in a red color. Is that correct?

Thanks for your help,

Bob

 

[Gord Dibben]

The code Bernie gave you is to be placed into a general module in your workbook
and run from Tools>Macro>Macros.

The red lines are due to a doubling up of the .(dot) in each line.

Should be .Cells(myR, 1).Formula = "='" & ActiveSheet.Name & "'!A1"

Yes, change the A1, B2 etc. to your cell references.


Gord Dibben MS Excel MVP

 

[robert morris]

Gord,

I'm at a complete loss. Nothing works. I entered the code in the general
module of my worksheet 1 (summary) I removed one . (dot) from the line
as you said (red went away), Changed A1 to M75, B2 to D75, C3 to F73, etc
......... & Created (copy) a new worksheet from the Master sheet. All I can
figure is my links in the Master sheet are scattered over three rows and
seven cells but my Summary sheet has all of the seven links on one Row. The
way I see the code, excel has no way to know where to put the links in the
summary sheet. M75 on the Master sheet should link to F10 on the summary
sheet, D75 to F10, F73 to I10, H73 to J10, J73 to K10, L73 to L10, M71 to
M10.

Does this change things?

Bob



Any ideas?

Bob

 

[Bernie Deitrick]

Robert,

We need clarification on a few points.

When you talk about worksheets, do you mean sheets within a workbook, or separate workbooks? A
workbook is a file with a unique name, say Summary.xls, and that workbook can have multiple
worksheets, which are the 'tabs' within the workbook. Those can also have names, like Summary, or
Master, etc. So if you explain your file/workbook/worksheet structure better, we can help you with
the macro. Names, sheet names, cells, etc...

If you have real problems, you can email the workbook(s) to me privately...

HTH,
Bernie
MS Excel MVP

 

[robert morris]

Bernie,

This is only one workbook titled March 2008. All others in the workbook are
worksheets (tabs) starting with (w/s 1, Summary), (w/s 2, Master, where
criteria is stored and never changed), all other w/s's are indiviual copies
of the Master with different names which are updated weekly. As I copy the
Master to a new worksheet with a new name, I need the new worksheet to add
the seven links to a new row in worksheet (summary) 1. Assuming the last row
occupied in the summary worksheet was Row 10, then the links from the newly
created w/s would populate Row 11.

Does this help? Again, thanks to you people who help beginners like me so
much.

Bob

 

[Bernie Deitrick]

After you copy the master sheet and rename it, try running this macro. I've assumed that your
summary sheet's name is Summary, and that the first used column of that summary sheet is column F -
if you have a different column, change the "F" in this line

myR = .Range("F" & Rows.Count).End(xlUp)(2).Row

to the first column's letter, like "D".

Also, I've based the code on this:

Since you were putting two things into F10, I moved the second into G. Note that this skips H and I,
but....

M75 => F
D75 => G
H73 => J
J73 => K
L73 => L
M71 => M



Sub MakeLinks()
Dim myR As Long
With Worksheets("Summary")
myR = .Range("F" & Rows.Count).End(xlUp)(2).Row
..Range("F" & myR).Formula = "='" & ActiveSheet.Name & "'!M75"
..Range("G" & myR).Formula = "='" & ActiveSheet.Name & "'!D75"
..Range("J" & myR).Formula = "='" & ActiveSheet.Name & "'!H73"
..Range("K" & myR).Formula = "='" & ActiveSheet.Name & "'!J73"
..Range("L" & myR).Formula = "='" & ActiveSheet.Name & "'!L73"
..Range("M" & myR).Formula = "='" & ActiveSheet.Name & "'!M71"
End With
End Sub

HTH,
Bernie
MS Excel MVP

 

[robert morris]

Bernie,

WORKS BEAUTIFULLY! You have no idea how much I admire the help you and the
rest of the helpmates on this site are appreciated. Although I understand
what the codes and some of the lines of code mean, I have no idea where to
start. I'm 77 years old and just learning Excel (self taught) over the last
year or so. I would love to try and learn the basics of VBA but, I don't
know where to go. I see a lot of things on the net but which one is best for
Beginning 101? Any suggestions? For your info, I study the formulas in the
discussions group and try to understand all the different solutions I see
there.

Again, thanks so much

Bob

 

[robert morris]

Bernie,

Everything works beautifully. I have a question. After I Rename a new
worksheet, can the tab be linked to the same line in the Summary sheet. Say,
Tab renamed to "Jones" linked to Column C in the summary sheet.

Being lazy, I'm looking for things to make life easy.

Bob

 

[Bernie Deitrick]

Robert,

Once you create the links, you can rename the sheet anything you want, and Excel will correctly
update any reference to that sheet.

HTH,
Bernie
MS Excel MVP

 

[robert morris]

Bernie,

Thanks again, I think I confused myself. What I meant to ask was, when I
copy the Master to a new sheet, I rename the new sheet, then I change the
Name Box B2 manually on the newly created sheet. If I could link the Tab
(New Name) to B2 in the SAME sheet, it would save one typing of the name.

Sorry for the incorrect posting.

Bob

 

[Bernie Deitrick]

That's OK.

Copy this code, and paste it into the code module of the Thisworkbook object:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$B$2" Then Sh.Name = Target.Value
End Sub


Then, any time that you enter a value into cell B2 of any sheet, the sheet name will change.

HTH,
Bernie
MS Excel MVP

 

[robert morris]

Bernie,

It is obvious I'm not putting the code in the correct place. Where is
"thisworkbook object" located? Is is my "Master Sheet which I copy to make a
new Sheet?

I hope to be out of your hair soon. I've been a pest, but I really
appreciate what you have done for me.

Bob

 

[Bernie Deitrick]

Robert,

In the project explorer, look for your workbook, and in the list will be an object named
ThisWorkbook. Double-click that, and the window that appears will be the codemodule of that object,
which handles the events at the workbook level for that workbook. See this

http://www.mvps.org/dmcritchie/excel/event.htm

for a much more thorough explanation.

HTH,
Bernie
MS Excel MVP

 

[robert morris]

Bernie,

Found it! Beautiful!! I learned again today with your help. Wish I could
return the favor someday.

Thanks again,

Bob