Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hello, is this message visible?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hello, is this message visible?
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 -- HTH, Bernie MS Excel MVP "robert morris" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hello, is this message visible?
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/mic...240999770fed54 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. -- David Biddulph "robert morris" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hello, is this message visible?
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 "Bernie Deitrick" wrote: 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 -- HTH, Bernie MS Excel MVP "robert morris" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hello, is this message visible?
The code Bernie gave you is to be placed into a general module in your workbook
and run from ToolsMacroMacros. 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 On Thu, 28 Feb 2008 18:16:00 -0800, robert morris wrote: 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 "Bernie Deitrick" wrote: 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 -- HTH, Bernie MS Excel MVP "robert morris" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hello, is this message visible?
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 "Gord Dibben" wrote: The code Bernie gave you is to be placed into a general module in your workbook and run from ToolsMacroMacros. 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 On Thu, 28 Feb 2008 18:16:00 -0800, robert morris wrote: 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 "Bernie Deitrick" wrote: 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 -- HTH, Bernie MS Excel MVP "robert morris" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hello, is this message visible?
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" wrote in message ... 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 "Gord Dibben" wrote: The code Bernie gave you is to be placed into a general module in your workbook and run from ToolsMacroMacros. 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 On Thu, 28 Feb 2008 18:16:00 -0800, robert morris wrote: 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 "Bernie Deitrick" wrote: 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 -- HTH, Bernie MS Excel MVP "robert morris" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hello, is this message visible?
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" wrote: 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" wrote in message ... 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 "Gord Dibben" wrote: The code Bernie gave you is to be placed into a general module in your workbook and run from ToolsMacroMacros. 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 On Thu, 28 Feb 2008 18:16:00 -0800, robert morris wrote: 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 "Bernie Deitrick" wrote: 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 -- HTH, Bernie MS Excel MVP "robert morris" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hello, is this message visible?
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: 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. 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 "Gord Dibben" wrote: The code Bernie gave you is to be placed into a general module in your workbook and run from ToolsMacroMacros. 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 On Thu, 28 Feb 2008 18:16:00 -0800, robert morris wrote: 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 "Bernie Deitrick" wrote: 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 -- HTH, Bernie MS Excel MVP "robert morris" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hello, is this message visible?
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 "Bernie Deitrick" wrote: 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: 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. 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 "Gord Dibben" wrote: The code Bernie gave you is to be placed into a general module in your workbook and run from ToolsMacroMacros. 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 On Thu, 28 Feb 2008 18:16:00 -0800, robert morris wrote: 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 "Bernie Deitrick" wrote: 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 -- HTH, Bernie MS Excel MVP "robert morris" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hello, is this message visible?
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" wrote: 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: 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. 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 "Gord Dibben" wrote: The code Bernie gave you is to be placed into a general module in your workbook and run from ToolsMacroMacros. 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 On Thu, 28 Feb 2008 18:16:00 -0800, robert morris wrote: 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 "Bernie Deitrick" wrote: 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 -- HTH, Bernie MS Excel MVP "robert morris" wrote in message ... 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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hello, is this message visible?
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" wrote in message ... 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" wrote: 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: 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. 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 "Gord Dibben" wrote: The code Bernie gave you is to be placed into a general module in your workbook and run from ToolsMacroMacros. 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 On Thu, 28 Feb 2008 18:16:00 -0800, robert morris wrote: 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 "Bernie Deitrick" wrote: 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 -- HTH, Bernie MS Excel MVP "robert morris" wrote in message ... 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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hello, is this message visible?
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" wrote: 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" wrote in message ... 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" wrote: 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: 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. 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 "Gord Dibben" wrote: The code Bernie gave you is to be placed into a general module in your workbook and run from ToolsMacroMacros. 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 On Thu, 28 Feb 2008 18:16:00 -0800, robert morris wrote: 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 "Bernie Deitrick" wrote: 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 -- HTH, Bernie MS Excel MVP "robert morris" wrote in message ... 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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hello, is this message visible?
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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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: 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. 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 "Gord Dibben" wrote: The code Bernie gave you is to be placed into a general module in your workbook and run from ToolsMacroMacros. 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 On Thu, 28 Feb 2008 18:16:00 -0800, robert morris wrote: 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 "Bernie Deitrick" wrote: 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 -- HTH, Bernie MS Excel MVP "robert morris" wrote in message ... 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 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hello, is this message visible?
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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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: 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. 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 "Gord Dibben" wrote: The code Bernie gave you is to be placed into a general module in your workbook and run from ToolsMacroMacros. 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 On Thu, 28 Feb 2008 18:16:00 -0800, robert morris wrote: 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 "Bernie Deitrick" wrote: 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 -- HTH, Bernie MS Excel MVP "robert morris" wrote in message ... 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 |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hello, is this message visible?
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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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: 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. 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 "Gord Dibben" wrote: The code Bernie gave you is to be placed into a general module in your workbook and run from ToolsMacroMacros. 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 On Thu, 28 Feb 2008 18:16:00 -0800, robert morris wrote: 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 "Bernie Deitrick" wrote: 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 -- HTH, Bernie MS Excel MVP "robert morris" wrote in message ... 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 |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hello, is this message visible?
Bernie,
Found it! Beautiful!! I learned again today with your help. Wish I could return the favor someday. Thanks again, Bob "Bernie Deitrick" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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: 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. 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 "Gord Dibben" wrote: The code Bernie gave you is to be placed into a general module in your workbook and run from ToolsMacroMacros. 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 On Thu, 28 Feb 2008 18:16:00 -0800, robert morris wrote: 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 "Bernie Deitrick" wrote: 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 -- HTH, Bernie MS Excel MVP "robert morris" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum visible row only | Excel Discussion (Misc queries) | |||
how can i do a (fx) visible in a cell | Excel Worksheet Functions | |||
visible formula | Excel Worksheet Functions | |||
changing the message in an error message | Excel Worksheet Functions | |||
Autoshapes not visible on spreadsheet but visible in print preview | Excel Discussion (Misc queries) |