| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Link - Previous post unclear
I have asked before but I'm afraid my posts were unclear. I have a "Summary" Worksheet and 4 "Weekly" Worksheets. I need to link only two cells from the "Weekly" sheets to the "Summary" sheet. i.e. "Worksheet "Week One" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week One" sheet Col C10:C39 to Col F8:F37 in "Summary" sheet. "Worksheet "Week Two" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Two" sheet Col C10:C39 to Col G8:G37 in "Summary" sheet. "Worksheet "Week Three" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Three" sheet Col C10:C39 to Col H8:H37 in "Summary" sheet. Worksheet "Week Four" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Four" sheet Col C10:C39 to Col I8:I37 in "Summary" sheet. All "Points" (Col C10) in "Weekly" sheets are Formula driven. I need to maintain the relationship between the Names and Points in all 5 Worksheets Perhaps this is a bit clearer. Any help? Bob |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA Link - Previous post unclear
Robert
You say "I need to link only two cells from the "Weekly" sheets to the "Summary" sheet." But then you talk about "sending" data from some 38 cells in each weekly sheet to the Summary sheet. No, this is not any clearer. What do you mean when you say "send"? Do you want one cell simply linked to another cell in another sheet? It would be much clearer if you just pretended to be doing all this manually and telling someone, who knows nothing about what you doing, what you are doing, in great detail. HTH Otto "robert morris" wrote in message ... I have asked before but I'm afraid my posts were unclear. I have a "Summary" Worksheet and 4 "Weekly" Worksheets. I need to link only two cells from the "Weekly" sheets to the "Summary" sheet. i.e. "Worksheet "Week One" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week One" sheet Col C10:C39 to Col F8:F37 in "Summary" sheet. "Worksheet "Week Two" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Two" sheet Col C10:C39 to Col G8:G37 in "Summary" sheet. "Worksheet "Week Three" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Three" sheet Col C10:C39 to Col H8:H37 in "Summary" sheet. Worksheet "Week Four" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Four" sheet Col C10:C39 to Col I8:I37 in "Summary" sheet. All "Points" (Col C10) in "Weekly" sheets are Formula driven. I need to maintain the relationship between the Names and Points in all 5 Worksheets Perhaps this is a bit clearer. Any help? Bob |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA Link - Previous post unclear
Otto
Thanks for your comments. I think what I meant with the two cells comment was, I thought I might just copy the the Columns down as needed. As for the "send", I thought as I posted the Weekly Worksheet, Excel would post the needed data in the Summary sheet. In my layman's language, I need to have the contents of Cell C10:C39 of the Week's results populate the Cells of F8:F37 in the Summary Sheet. The same applies to Week's 2, 3 and 4 except for the Col's G, H and I. Does this help? Bob "Otto Moehrbach" wrote: Robert You say "I need to link only two cells from the "Weekly" sheets to the "Summary" sheet." But then you talk about "sending" data from some 38 cells in each weekly sheet to the Summary sheet. No, this is not any clearer. What do you mean when you say "send"? Do you want one cell simply linked to another cell in another sheet? It would be much clearer if you just pretended to be doing all this manually and telling someone, who knows nothing about what you doing, what you are doing, in great detail. HTH Otto "robert morris" wrote in message ... I have asked before but I'm afraid my posts were unclear. I have a "Summary" Worksheet and 4 "Weekly" Worksheets. I need to link only two cells from the "Weekly" sheets to the "Summary" sheet. i.e. "Worksheet "Week One" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week One" sheet Col C10:C39 to Col F8:F37 in "Summary" sheet. "Worksheet "Week Two" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Two" sheet Col C10:C39 to Col G8:G37 in "Summary" sheet. "Worksheet "Week Three" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Three" sheet Col C10:C39 to Col H8:H37 in "Summary" sheet. Worksheet "Week Four" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Four" sheet Col C10:C39 to Col I8:I37 in "Summary" sheet. All "Points" (Col C10) in "Weekly" sheets are Formula driven. I need to maintain the relationship between the Names and Points in all 5 Worksheets Perhaps this is a bit clearer. Any help? Bob |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA Link - Previous post unclear
Robert
Are you saying that you want cells C10:C39 of the First week sheet copied to cells F8:F37 of the Summary sheet? And week 2's data would go into column G, week 3's to column H, and week 4's to column I? That's easy to do. Now the question: What do you want to use as the trigger to make this happen? You say "...I thought as I posted the Weekly Worksheet, Excel would post the needed data in the Summary sheet." I take "post" to mean that you enter data in the weekly sheet. That's fine, but you are entering data in some 19 cells. Excel needs a specific event to act on. Maybe when you enter data into cell C39? Would that work for you? Also, Excel needs to know something about the name of each week sheet or something about each week sheet so that Excel will know where to put the data. In other words, what about each sheet identifies that sheet as week 1 or week 2, etc? Also, are there just 4 week sheets? Are there any non-week sheets besides the Summary sheet? Excel needs to know to work on this sheet but not on that sheet. Otto "robert morris" wrote in message ... Otto Thanks for your comments. I think what I meant with the two cells comment was, I thought I might just copy the the Columns down as needed. As for the "send", I thought as I posted the Weekly Worksheet, Excel would post the needed data in the Summary sheet. In my layman's language, I need to have the contents of Cell C10:C39 of the Week's results populate the Cells of F8:F37 in the Summary Sheet. The same applies to Week's 2, 3 and 4 except for the Col's G, H and I. Does this help? Bob "Otto Moehrbach" wrote: Robert You say "I need to link only two cells from the "Weekly" sheets to the "Summary" sheet." But then you talk about "sending" data from some 38 cells in each weekly sheet to the Summary sheet. No, this is not any clearer. What do you mean when you say "send"? Do you want one cell simply linked to another cell in another sheet? It would be much clearer if you just pretended to be doing all this manually and telling someone, who knows nothing about what you doing, what you are doing, in great detail. HTH Otto "robert morris" wrote in message ... I have asked before but I'm afraid my posts were unclear. I have a "Summary" Worksheet and 4 "Weekly" Worksheets. I need to link only two cells from the "Weekly" sheets to the "Summary" sheet. i.e. "Worksheet "Week One" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week One" sheet Col C10:C39 to Col F8:F37 in "Summary" sheet. "Worksheet "Week Two" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Two" sheet Col C10:C39 to Col G8:G37 in "Summary" sheet. "Worksheet "Week Three" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Three" sheet Col C10:C39 to Col H8:H37 in "Summary" sheet. Worksheet "Week Four" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Four" sheet Col C10:C39 to Col I8:I37 in "Summary" sheet. All "Points" (Col C10) in "Weekly" sheets are Formula driven. I need to maintain the relationship between the Names and Points in all 5 Worksheets Perhaps this is a bit clearer. Any help? Bob |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA Link - Previous post unclear
Otto,
Sorry for the delay in my answer. Someone ran over the Telephone Terminal Box for my neighborhood and the phone company just finished the repairs. I have not had a phone or internet for almost a full day. Yes, C10:C39 copies to F8:F37 of the Summary Sheet, C10:C39 of Week Two to G8:G37 of the Summary, ect. Each of the four Weekly Sheets has the persons name in Column E10:E39 which should also copy to the Summary Sheet Column C8:C37. I "sort" each Week's scores from Col C and the Names and Scores for each person need to remain together. After each Week's postings to the Standings Sheet, it then is also "Sorted" As to the "trigger", I have another Workbook (unrelated to this one) in which I use a Macro to "post" the info to the Summary Sheet. A Macro would work better for this application as Column C of the Weekly Sheets is the Result of 9 other catagories on the Weekly report. The Weekly Sheets are simply identified as Week One, Week Two, Week Three & Week Four. At this point, the 4 Weeks Reports and the Summary Sheet are all that are in this Workbook, although at some point in time I could expand the 4 Weekly's to possibly 12 but not now. Thanks for your interest. Bob "Otto Moehrbach" wrote: Robert Are you saying that you want cells C10:C39 of the First week sheet copied to cells F8:F37 of the Summary sheet? And week 2's data would go into column G, week 3's to column H, and week 4's to column I? That's easy to do. Now the question: What do you want to use as the trigger to make this happen? You say "...I thought as I posted the Weekly Worksheet, Excel would post the needed data in the Summary sheet." I take "post" to mean that you enter data in the weekly sheet. That's fine, but you are entering data in some 19 cells. Excel needs a specific event to act on. Maybe when you enter data into cell C39? Would that work for you? Also, Excel needs to know something about the name of each week sheet or something about each week sheet so that Excel will know where to put the data. In other words, what about each sheet identifies that sheet as week 1 or week 2, etc? Also, are there just 4 week sheets? Are there any non-week sheets besides the Summary sheet? Excel needs to know to work on this sheet but not on that sheet. Otto "robert morris" wrote in message ... Otto Thanks for your comments. I think what I meant with the two cells comment was, I thought I might just copy the the Columns down as needed. As for the "send", I thought as I posted the Weekly Worksheet, Excel would post the needed data in the Summary sheet. In my layman's language, I need to have the contents of Cell C10:C39 of the Week's results populate the Cells of F8:F37 in the Summary Sheet. The same applies to Week's 2, 3 and 4 except for the Col's G, H and I. Does this help? Bob "Otto Moehrbach" wrote: Robert You say "I need to link only two cells from the "Weekly" sheets to the "Summary" sheet." But then you talk about "sending" data from some 38 cells in each weekly sheet to the Summary sheet. No, this is not any clearer. What do you mean when you say "send"? Do you want one cell simply linked to another cell in another sheet? It would be much clearer if you just pretended to be doing all this manually and telling someone, who knows nothing about what you doing, what you are doing, in great detail. HTH Otto "robert morris" wrote in message ... I have asked before but I'm afraid my posts were unclear. I have a "Summary" Worksheet and 4 "Weekly" Worksheets. I need to link only two cells from the "Weekly" sheets to the "Summary" sheet. i.e. "Worksheet "Week One" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week One" sheet Col C10:C39 to Col F8:F37 in "Summary" sheet. "Worksheet "Week Two" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Two" sheet Col C10:C39 to Col G8:G37 in "Summary" sheet. "Worksheet "Week Three" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Three" sheet Col C10:C39 to Col H8:H37 in "Summary" sheet. Worksheet "Week Four" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Four" sheet Col C10:C39 to Col I8:I37 in "Summary" sheet. All "Points" (Col C10) in "Weekly" sheets are Formula driven. I need to maintain the relationship between the Names and Points in all 5 Worksheets Perhaps this is a bit clearer. Any help? Bob |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA Link - Previous post unclear
Robert
You misunderstood what I meant by "the trigger". Yes, a macro will have to be written to do this. What I was asking was what do you want to use as the trigger that makes the macro run? You can initiate the macro manually but I gathered from what you said before that you wanted the data to be placed in the Summary sheet when you put it in the week sheets. That implies some sort of automation to trigger the macro to run. So I'm asking you when do you want this macro to run? The names thing is a facet of this that you hadn't mentioned before. You say the names are in E10:E37 of each week sheet. Are the names in each week sheet different? If so, in what column of the Summary sheet do you want the names from each week sheet? Or maybe all the names are the same for each week sheet and you want just one set of names placed in E10:E37 of the Summary sheet? Otto "robert morris" wrote in message ... Otto, Sorry for the delay in my answer. Someone ran over the Telephone Terminal Box for my neighborhood and the phone company just finished the repairs. I have not had a phone or internet for almost a full day. Yes, C10:C39 copies to F8:F37 of the Summary Sheet, C10:C39 of Week Two to G8:G37 of the Summary, ect. Each of the four Weekly Sheets has the persons name in Column E10:E39 which should also copy to the Summary Sheet Column C8:C37. I "sort" each Week's scores from Col C and the Names and Scores for each person need to remain together. After each Week's postings to the Standings Sheet, it then is also "Sorted" As to the "trigger", I have another Workbook (unrelated to this one) in which I use a Macro to "post" the info to the Summary Sheet. A Macro would work better for this application as Column C of the Weekly Sheets is the Result of 9 other catagories on the Weekly report. The Weekly Sheets are simply identified as Week One, Week Two, Week Three & Week Four. At this point, the 4 Weeks Reports and the Summary Sheet are all that are in this Workbook, although at some point in time I could expand the 4 Weekly's to possibly 12 but not now. Thanks for your interest. Bob "Otto Moehrbach" wrote: Robert Are you saying that you want cells C10:C39 of the First week sheet copied to cells F8:F37 of the Summary sheet? And week 2's data would go into column G, week 3's to column H, and week 4's to column I? That's easy to do. Now the question: What do you want to use as the trigger to make this happen? You say "...I thought as I posted the Weekly Worksheet, Excel would post the needed data in the Summary sheet." I take "post" to mean that you enter data in the weekly sheet. That's fine, but you are entering data in some 19 cells. Excel needs a specific event to act on. Maybe when you enter data into cell C39? Would that work for you? Also, Excel needs to know something about the name of each week sheet or something about each week sheet so that Excel will know where to put the data. In other words, what about each sheet identifies that sheet as week 1 or week 2, etc? Also, are there just 4 week sheets? Are there any non-week sheets besides the Summary sheet? Excel needs to know to work on this sheet but not on that sheet. Otto "robert morris" wrote in message ... Otto Thanks for your comments. I think what I meant with the two cells comment was, I thought I might just copy the the Columns down as needed. As for the "send", I thought as I posted the Weekly Worksheet, Excel would post the needed data in the Summary sheet. In my layman's language, I need to have the contents of Cell C10:C39 of the Week's results populate the Cells of F8:F37 in the Summary Sheet. The same applies to Week's 2, 3 and 4 except for the Col's G, H and I. Does this help? Bob "Otto Moehrbach" wrote: Robert You say "I need to link only two cells from the "Weekly" sheets to the "Summary" sheet." But then you talk about "sending" data from some 38 cells in each weekly sheet to the Summary sheet. No, this is not any clearer. What do you mean when you say "send"? Do you want one cell simply linked to another cell in another sheet? It would be much clearer if you just pretended to be doing all this manually and telling someone, who knows nothing about what you doing, what you are doing, in great detail. HTH Otto "robert morris" wrote in message ... I have asked before but I'm afraid my posts were unclear. I have a "Summary" Worksheet and 4 "Weekly" Worksheets. I need to link only two cells from the "Weekly" sheets to the "Summary" sheet. i.e. "Worksheet "Week One" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week One" sheet Col C10:C39 to Col F8:F37 in "Summary" sheet. "Worksheet "Week Two" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Two" sheet Col C10:C39 to Col G8:G37 in "Summary" sheet. "Worksheet "Week Three" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Three" sheet Col C10:C39 to Col H8:H37 in "Summary" sheet. Worksheet "Week Four" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Four" sheet Col C10:C39 to Col I8:I37 in "Summary" sheet. All "Points" (Col C10) in "Weekly" sheets are Formula driven. I need to maintain the relationship between the Names and Points in all 5 Worksheets Perhaps this is a bit clearer. Any help? Bob |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA Link - Previous post unclear
Otto,
I use Ctrl Z on my other similar Workbook. Works fine. As to the "Names", I have shown them on all my posts. The "Names" are the same in all 4 Weekly Sheets in Column E10:E39 and are in Col C8:C37 in the Summary Sheet and need to maintain the integrity of Name and Results of each person. I'm sorry my explainations are so vague. I think I need a "101 class" in asking my questions properly. Thanks, Bob "Otto Moehrbach" wrote: Robert You misunderstood what I meant by "the trigger". Yes, a macro will have to be written to do this. What I was asking was what do you want to use as the trigger that makes the macro run? You can initiate the macro manually but I gathered from what you said before that you wanted the data to be placed in the Summary sheet when you put it in the week sheets. That implies some sort of automation to trigger the macro to run. So I'm asking you when do you want this macro to run? The names thing is a facet of this that you hadn't mentioned before. You say the names are in E10:E37 of each week sheet. Are the names in each week sheet different? If so, in what column of the Summary sheet do you want the names from each week sheet? Or maybe all the names are the same for each week sheet and you want just one set of names placed in E10:E37 of the Summary sheet? Otto "robert morris" wrote in message ... Otto, Sorry for the delay in my answer. Someone ran over the Telephone Terminal Box for my neighborhood and the phone company just finished the repairs. I have not had a phone or internet for almost a full day. Yes, C10:C39 copies to F8:F37 of the Summary Sheet, C10:C39 of Week Two to G8:G37 of the Summary, ect. Each of the four Weekly Sheets has the persons name in Column E10:E39 which should also copy to the Summary Sheet Column C8:C37. I "sort" each Week's scores from Col C and the Names and Scores for each person need to remain together. After each Week's postings to the Standings Sheet, it then is also "Sorted" As to the "trigger", I have another Workbook (unrelated to this one) in which I use a Macro to "post" the info to the Summary Sheet. A Macro would work better for this application as Column C of the Weekly Sheets is the Result of 9 other catagories on the Weekly report. The Weekly Sheets are simply identified as Week One, Week Two, Week Three & Week Four. At this point, the 4 Weeks Reports and the Summary Sheet are all that are in this Workbook, although at some point in time I could expand the 4 Weekly's to possibly 12 but not now. Thanks for your interest. Bob "Otto Moehrbach" wrote: Robert Are you saying that you want cells C10:C39 of the First week sheet copied to cells F8:F37 of the Summary sheet? And week 2's data would go into column G, week 3's to column H, and week 4's to column I? That's easy to do. Now the question: What do you want to use as the trigger to make this happen? You say "...I thought as I posted the Weekly Worksheet, Excel would post the needed data in the Summary sheet." I take "post" to mean that you enter data in the weekly sheet. That's fine, but you are entering data in some 19 cells. Excel needs a specific event to act on. Maybe when you enter data into cell C39? Would that work for you? Also, Excel needs to know something about the name of each week sheet or something about each week sheet so that Excel will know where to put the data. In other words, what about each sheet identifies that sheet as week 1 or week 2, etc? Also, are there just 4 week sheets? Are there any non-week sheets besides the Summary sheet? Excel needs to know to work on this sheet but not on that sheet. Otto "robert morris" wrote in message ... Otto Thanks for your comments. I think what I meant with the two cells comment was, I thought I might just copy the the Columns down as needed. As for the "send", I thought as I posted the Weekly Worksheet, Excel would post the needed data in the Summary sheet. In my layman's language, I need to have the contents of Cell C10:C39 of the Week's results populate the Cells of F8:F37 in the Summary Sheet. The same applies to Week's 2, 3 and 4 except for the Col's G, H and I. Does this help? Bob "Otto Moehrbach" wrote: Robert You say "I need to link only two cells from the "Weekly" sheets to the "Summary" sheet." But then you talk about "sending" data from some 38 cells in each weekly sheet to the Summary sheet. No, this is not any clearer. What do you mean when you say "send"? Do you want one cell simply linked to another cell in another sheet? It would be much clearer if you just pretended to be doing all this manually and telling someone, who knows nothing about what you doing, what you are doing, in great detail. HTH Otto "robert morris" wrote in message ... I have asked before but I'm afraid my posts were unclear. I have a "Summary" Worksheet and 4 "Weekly" Worksheets. I need to link only two cells from the "Weekly" sheets to the "Summary" sheet. i.e. "Worksheet "Week One" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week One" sheet Col C10:C39 to Col F8:F37 in "Summary" sheet. "Worksheet "Week Two" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Two" sheet Col C10:C39 to Col G8:G37 in "Summary" sheet. "Worksheet "Week Three" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Three" sheet Col C10:C39 to Col H8:H37 in "Summary" sheet. Worksheet "Week Four" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Four" sheet Col C10:C39 to Col I8:I37 in "Summary" sheet. All "Points" (Col C10) in "Weekly" sheets are Formula driven. I need to maintain the relationship between the Names and Points in all 5 Worksheets Perhaps this is a bit clearer. Any help? Bob |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA Link - Previous post unclear
Robert
Here is a macro that should be what you're looking for. Come back if you need more or you need to change it. The trigger: You said that you use Ctrl-Z in your other file. That's fine and you can use the same thing on this file. But be aware that Excel has a number of built-in key shortcuts that use this same format, Ctrl-X, where X is some letter. You don't want to tangle with any of the built-in shortcut keys and the best way to ensure that you don't is to use Ctrl-Shift-X where X is the letter of your choice. Either way, you need to set this up in your file yourself. This macro is written with the following assumptions: The file has a sheet named "Summary". The file can contain many other sheets with many other names, but Excel will work with ONLY those sheets have "Week" as the first 4 characters in the sheet name and whose names appear in the "Select Case" command in the macro. These sheet names in the "Select Case" command MUST MATCH EXACTLY the actual names of the week sheets. You can add more week sheets as you wish and modify the "Select Case" command by simply adding "Case" lines. Note that "TheCol" is the column number in the Summary sheet in which you want the particular week sheet data to go. This macro copies C10:C39 in each week sheet and places it in row 8 of the appropriate column in the Summary sheet. It also copies E10:E39 in the "Week One" sheet and places that in C8 of the Summary sheet. Don't fret needing "101 class" when asking questions. Unless you are familiar with programming, it is virtually impossible to realize the level of minute detail that must be known when writing code. HTH Otto Sub PostData() Dim ws As Worksheet Dim TheCol As Long Application.ScreenUpdating = False Sheets("Summary").Select For Each ws In ThisWorkbook.Worksheets If Left(ws.Name, 4) = "Week" Then Select Case ws.Name Case "Week One": TheCol = 6 Case "Week Two": TheCol = 7 Case "Week Three": TheCol = 8 Case "Week Four": TheCol = 9 End Select With ws .Range("C10:C39").Copy Cells(8, TheCol) If ws.Name = "Week One" Then _ .Range("E10:E39").Copy Range("C8") End With End If Next ws Application.ScreenUpdating = True End Sub "robert morris" wrote in message ... Otto, I use Ctrl Z on my other similar Workbook. Works fine. As to the "Names", I have shown them on all my posts. The "Names" are the same in all 4 Weekly Sheets in Column E10:E39 and are in Col C8:C37 in the Summary Sheet and need to maintain the integrity of Name and Results of each person. I'm sorry my explainations are so vague. I think I need a "101 class" in asking my questions properly. Thanks, Bob "Otto Moehrbach" wrote: Robert You misunderstood what I meant by "the trigger". Yes, a macro will have to be written to do this. What I was asking was what do you want to use as the trigger that makes the macro run? You can initiate the macro manually but I gathered from what you said before that you wanted the data to be placed in the Summary sheet when you put it in the week sheets. That implies some sort of automation to trigger the macro to run. So I'm asking you when do you want this macro to run? The names thing is a facet of this that you hadn't mentioned before. You say the names are in E10:E37 of each week sheet. Are the names in each week sheet different? If so, in what column of the Summary sheet do you want the names from each week sheet? Or maybe all the names are the same for each week sheet and you want just one set of names placed in E10:E37 of the Summary sheet? Otto "robert morris" wrote in message ... Otto, Sorry for the delay in my answer. Someone ran over the Telephone Terminal Box for my neighborhood and the phone company just finished the repairs. I have not had a phone or internet for almost a full day. Yes, C10:C39 copies to F8:F37 of the Summary Sheet, C10:C39 of Week Two to G8:G37 of the Summary, ect. Each of the four Weekly Sheets has the persons name in Column E10:E39 which should also copy to the Summary Sheet Column C8:C37. I "sort" each Week's scores from Col C and the Names and Scores for each person need to remain together. After each Week's postings to the Standings Sheet, it then is also "Sorted" As to the "trigger", I have another Workbook (unrelated to this one) in which I use a Macro to "post" the info to the Summary Sheet. A Macro would work better for this application as Column C of the Weekly Sheets is the Result of 9 other catagories on the Weekly report. The Weekly Sheets are simply identified as Week One, Week Two, Week Three & Week Four. At this point, the 4 Weeks Reports and the Summary Sheet are all that are in this Workbook, although at some point in time I could expand the 4 Weekly's to possibly 12 but not now. Thanks for your interest. Bob "Otto Moehrbach" wrote: Robert Are you saying that you want cells C10:C39 of the First week sheet copied to cells F8:F37 of the Summary sheet? And week 2's data would go into column G, week 3's to column H, and week 4's to column I? That's easy to do. Now the question: What do you want to use as the trigger to make this happen? You say "...I thought as I posted the Weekly Worksheet, Excel would post the needed data in the Summary sheet." I take "post" to mean that you enter data in the weekly sheet. That's fine, but you are entering data in some 19 cells. Excel needs a specific event to act on. Maybe when you enter data into cell C39? Would that work for you? Also, Excel needs to know something about the name of each week sheet or something about each week sheet so that Excel will know where to put the data. In other words, what about each sheet identifies that sheet as week 1 or week 2, etc? Also, are there just 4 week sheets? Are there any non-week sheets besides the Summary sheet? Excel needs to know to work on this sheet but not on that sheet. Otto "robert morris" wrote in message ... Otto Thanks for your comments. I think what I meant with the two cells comment was, I thought I might just copy the the Columns down as needed. As for the "send", I thought as I posted the Weekly Worksheet, Excel would post the needed data in the Summary sheet. In my layman's language, I need to have the contents of Cell C10:C39 of the Week's results populate the Cells of F8:F37 in the Summary Sheet. The same applies to Week's 2, 3 and 4 except for the Col's G, H and I. Does this help? Bob "Otto Moehrbach" wrote: Robert You say "I need to link only two cells from the "Weekly" sheets to the "Summary" sheet." But then you talk about "sending" data from some 38 cells in each weekly sheet to the Summary sheet. No, this is not any clearer. What do you mean when you say "send"? Do you want one cell simply linked to another cell in another sheet? It would be much clearer if you just pretended to be doing all this manually and telling someone, who knows nothing about what you doing, what you are doing, in great detail. HTH Otto "robert morris" wrote in message ... I have asked before but I'm afraid my posts were unclear. I have a "Summary" Worksheet and 4 "Weekly" Worksheets. I need to link only two cells from the "Weekly" sheets to the "Summary" sheet. i.e. "Worksheet "Week One" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week One" sheet Col C10:C39 to Col F8:F37 in "Summary" sheet. "Worksheet "Week Two" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Two" sheet Col C10:C39 to Col G8:G37 in "Summary" sheet. "Worksheet "Week Three" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Three" sheet Col C10:C39 to Col H8:H37 in "Summary" sheet. Worksheet "Week Four" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Four" sheet Col C10:C39 to Col I8:I37 in "Summary" sheet. All "Points" (Col C10) in "Weekly" sheets are Formula driven. I need to maintain the relationship between the Names and Points in all 5 Worksheets Perhaps this is a bit clearer. Any help? Bob |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA Link - Previous post unclear
Otto,
This is close to working. I copied the VBA?Macro to the Summary Sheet. Used Ctrl z to run. "Names" in E10:E39 from Week One (that is all I have worked on) copied to proper column C8:C37 of the Summary Sheet. Problem is, the "Names" also copied to Col F of the Summary. The numbers/data in Col C10:C39 of Week One should copy to Col F of the Summary. Bob "Otto Moehrbach" wrote: Robert Here is a macro that should be what you're looking for. Come back if you need more or you need to change it. The trigger: You said that you use Ctrl-Z in your other file. That's fine and you can use the same thing on this file. But be aware that Excel has a number of built-in key shortcuts that use this same format, Ctrl-X, where X is some letter. You don't want to tangle with any of the built-in shortcut keys and the best way to ensure that you don't is to use Ctrl-Shift-X where X is the letter of your choice. Either way, you need to set this up in your file yourself. This macro is written with the following assumptions: The file has a sheet named "Summary". The file can contain many other sheets with many other names, but Excel will work with ONLY those sheets have "Week" as the first 4 characters in the sheet name and whose names appear in the "Select Case" command in the macro. These sheet names in the "Select Case" command MUST MATCH EXACTLY the actual names of the week sheets. You can add more week sheets as you wish and modify the "Select Case" command by simply adding "Case" lines. Note that "TheCol" is the column number in the Summary sheet in which you want the particular week sheet data to go. This macro copies C10:C39 in each week sheet and places it in row 8 of the appropriate column in the Summary sheet. It also copies E10:E39 in the "Week One" sheet and places that in C8 of the Summary sheet. Don't fret needing "101 class" when asking questions. Unless you are familiar with programming, it is virtually impossible to realize the level of minute detail that must be known when writing code. HTH Otto Sub PostData() Dim ws As Worksheet Dim TheCol As Long Application.ScreenUpdating = False Sheets("Summary").Select For Each ws In ThisWorkbook.Worksheets If Left(ws.Name, 4) = "Week" Then Select Case ws.Name Case "Week One": TheCol = 6 Case "Week Two": TheCol = 7 Case "Week Three": TheCol = 8 Case "Week Four": TheCol = 9 End Select With ws .Range("C10:C39").Copy Cells(8, TheCol) If ws.Name = "Week One" Then _ .Range("E10:E39").Copy Range("C8") End With End If Next ws Application.ScreenUpdating = True End Sub "robert morris" wrote in message ... Otto, I use Ctrl Z on my other similar Workbook. Works fine. As to the "Names", I have shown them on all my posts. The "Names" are the same in all 4 Weekly Sheets in Column E10:E39 and are in Col C8:C37 in the Summary Sheet and need to maintain the integrity of Name and Results of each person. I'm sorry my explainations are so vague. I think I need a "101 class" in asking my questions properly. Thanks, Bob "Otto Moehrbach" wrote: Robert You misunderstood what I meant by "the trigger". Yes, a macro will have to be written to do this. What I was asking was what do you want to use as the trigger that makes the macro run? You can initiate the macro manually but I gathered from what you said before that you wanted the data to be placed in the Summary sheet when you put it in the week sheets. That implies some sort of automation to trigger the macro to run. So I'm asking you when do you want this macro to run? The names thing is a facet of this that you hadn't mentioned before. You say the names are in E10:E37 of each week sheet. Are the names in each week sheet different? If so, in what column of the Summary sheet do you want the names from each week sheet? Or maybe all the names are the same for each week sheet and you want just one set of names placed in E10:E37 of the Summary sheet? Otto "robert morris" wrote in message ... Otto, Sorry for the delay in my answer. Someone ran over the Telephone Terminal Box for my neighborhood and the phone company just finished the repairs. I have not had a phone or internet for almost a full day. Yes, C10:C39 copies to F8:F37 of the Summary Sheet, C10:C39 of Week Two to G8:G37 of the Summary, ect. Each of the four Weekly Sheets has the persons name in Column E10:E39 which should also copy to the Summary Sheet Column C8:C37. I "sort" each Week's scores from Col C and the Names and Scores for each person need to remain together. After each Week's postings to the Standings Sheet, it then is also "Sorted" As to the "trigger", I have another Workbook (unrelated to this one) in which I use a Macro to "post" the info to the Summary Sheet. A Macro would work better for this application as Column C of the Weekly Sheets is the Result of 9 other catagories on the Weekly report. The Weekly Sheets are simply identified as Week One, Week Two, Week Three & Week Four. At this point, the 4 Weeks Reports and the Summary Sheet are all that are in this Workbook, although at some point in time I could expand the 4 Weekly's to possibly 12 but not now. Thanks for your interest. Bob "Otto Moehrbach" wrote: Robert Are you saying that you want cells C10:C39 of the First week sheet copied to cells F8:F37 of the Summary sheet? And week 2's data would go into column G, week 3's to column H, and week 4's to column I? That's easy to do. Now the question: What do you want to use as the trigger to make this happen? You say "...I thought as I posted the Weekly Worksheet, Excel would post the needed data in the Summary sheet." I take "post" to mean that you enter data in the weekly sheet. That's fine, but you are entering data in some 19 cells. Excel needs a specific event to act on. Maybe when you enter data into cell C39? Would that work for you? Also, Excel needs to know something about the name of each week sheet or something about each week sheet so that Excel will know where to put the data. In other words, what about each sheet identifies that sheet as week 1 or week 2, etc? Also, are there just 4 week sheets? Are there any non-week sheets besides the Summary sheet? Excel needs to know to work on this sheet but not on that sheet. Otto "robert morris" wrote in message ... Otto Thanks for your comments. I think what I meant with the two cells comment was, I thought I might just copy the the Columns down as needed. As for the "send", I thought as I posted the Weekly Worksheet, Excel would post the needed data in the Summary sheet. In my layman's language, I need to have the contents of Cell C10:C39 of the Week's results populate the Cells of F8:F37 in the Summary Sheet. The same applies to Week's 2, 3 and 4 except for the Col's G, H and I. Does this help? Bob "Otto Moehrbach" wrote: Robert You say "I need to link only two cells from the "Weekly" sheets to the "Summary" sheet." But then you talk about "sending" data from some 38 cells in each weekly sheet to the Summary sheet. No, this is not any clearer. What do you mean when you say "send"? Do you want one cell simply linked to another cell in another sheet? It would be much clearer if you just pretended to be doing all this manually and telling someone, who knows nothing about what you doing, what you are doing, in great detail. HTH Otto "robert morris" wrote in message ... I have asked before but I'm afraid my posts were unclear. I have a "Summary" Worksheet and 4 "Weekly" Worksheets. I need to link only two cells from the "Weekly" sheets to the "Summary" sheet. i.e. "Worksheet "Week One" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week One" sheet Col C10:C39 to Col F8:F37 in "Summary" sheet. "Worksheet "Week Two" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Two" sheet Col C10:C39 to Col G8:G37 in "Summary" sheet. "Worksheet "Week Three" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Three" sheet Col C10:C39 to Col H8:H37 in "Summary" sheet. Worksheet "Week Four" sends data "Names" from Col E10:E39 to "Summary" sheet Col C8:C37 along with sending data "Points" in "Week Four" sheet Col C10:C39 to Col I8:I37 in "Summary" sheet. All "Points" (Col C10) in "Weekly" sheets are Formula driven. I need to maintain the relationship between the Names and Points in all 5 Worksheets Perhaps this is a bit clearer. Any help? Bob |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA Link - Previous post unclear
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Correction to previous post | Excel Worksheet Functions | |||
| Finding My previous post | Excel Discussion (Misc queries) | |||
| To Mr. Liengme: Re My previous Post Concerning My Bar Chart Problem | Charts and Charting in Excel | |||
| Cannot previous post - email via access to excel template | Links and Linking in Excel | |||
| Previous Post - Correct Syntax Query | Excel Worksheet Functions | |||
Linear Mode
