Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Summary page for 12 worksheets
I have a workbook with 12 worksheets and I need to sum 1 cell (ex. B37) on
each worksheet on one summary page. How can I track the cell from the different worksheets to add them? |
#2
|
|||
|
|||
Create two new worksheets--one to the far right and one to the far left.
Call them Start and End Then using a sheet (Summary) that is outside this "sandwich" of worksheets: =sum(start:end!B37) Then you can drag sheets in and out of that sandwich to play what if games. I'd put a couple of notes on each of these sheets: "don't delete this sheet!" And protect the worksheet so that people don't use it for real data. ACM wrote: I have a workbook with 12 worksheets and I need to sum 1 cell (ex. B37) on each worksheet on one summary page. How can I track the cell from the different worksheets to add them? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summary page for 12 worksheets
Dave:
Along these same lines: I have several worksheets which I need to combine into a summary page on the first tab. The workbook contans several worksheets (one for each day a job is worked) with the hours for each person. One sheet (day) may contain data for up to 15 guys. I need to be able to extract the date (B6) , class (GF, F, JW, AP) (C9:C44) and the number of hours of straight time, overtime and double time (the letters ST, OT and DT are in column E but the actual hours are in column F) for each individual class onto the summary page. The goal here is to be able to track how much I have remaining on a purchase order. My timesheets cannot be modified, a pivot table does not provide a way that I can find to do this easily and macros are frowned upon. I currently have a macro in use on this workbook that will create a new sheet via the duplication of the previous sheet but this may have to be deleted due to the company not liking macros created by an outside source. Can my dilemma be solved? If I have you confused, I could send you a sample of the workbook to your personal address if needed. Thank you for your time! "Dave Peterson" wrote: Create two new worksheets--one to the far right and one to the far left. Call them Start and End Then using a sheet (Summary) that is outside this "sandwich" of worksheets: =sum(start:end!B37) Then you can drag sheets in and out of that sandwich to play what if games. I'd put a couple of notes on each of these sheets: "don't delete this sheet!" And protect the worksheet so that people don't use it for real data. ACM wrote: I have a workbook with 12 worksheets and I need to sum 1 cell (ex. B37) on each worksheet on one summary page. How can I track the cell from the different worksheets to add them? -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summary page for 12 worksheets
I would use a macro--so that won't work for you.
You may want to post in the .worksheet.functions newsgroup. Maybe there's some giant formulas you could use (way beyond me!). MGC wrote: Dave: Along these same lines: I have several worksheets which I need to combine into a summary page on the first tab. The workbook contans several worksheets (one for each day a job is worked) with the hours for each person. One sheet (day) may contain data for up to 15 guys. I need to be able to extract the date (B6) , class (GF, F, JW, AP) (C9:C44) and the number of hours of straight time, overtime and double time (the letters ST, OT and DT are in column E but the actual hours are in column F) for each individual class onto the summary page. The goal here is to be able to track how much I have remaining on a purchase order. My timesheets cannot be modified, a pivot table does not provide a way that I can find to do this easily and macros are frowned upon. I currently have a macro in use on this workbook that will create a new sheet via the duplication of the previous sheet but this may have to be deleted due to the company not liking macros created by an outside source. Can my dilemma be solved? If I have you confused, I could send you a sample of the workbook to your personal address if needed. Thank you for your time! "Dave Peterson" wrote: Create two new worksheets--one to the far right and one to the far left. Call them Start and End Then using a sheet (Summary) that is outside this "sandwich" of worksheets: =sum(start:end!B37) Then you can drag sheets in and out of that sandwich to play what if games. I'd put a couple of notes on each of these sheets: "don't delete this sheet!" And protect the worksheet so that people don't use it for real data. ACM wrote: I have a workbook with 12 worksheets and I need to sum 1 cell (ex. B37) on each worksheet on one summary page. How can I track the cell from the different worksheets to add them? -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summary page for 12 worksheets
Hi
Pivot tables would work, there are just more fields to select than you led me to believe in your previous posting. Anyway, another alternative might be as follows. Ensure that Order number is the first column on all of your sheets. If they are not, then select first sheethold down ShiftSelect last sheet and this will Group the sheets. Select the column with Order NumberCutSelect column AInsert Cut cells Select any individual sheet tab to Ungroup the selection On a blank sheet that is to be your Summary sheet, choose DataConsolidate Leave the first dialogue box as Sum. Click on your first sheet, select all the columns that you needclick Addmove to second sheet, the range will later to reflect the new sheet nameClick Addrepeat until all sheets required have been selected. Click Use Label in Top row and Left ColumnOK This will give you Totals of Hours for each Order number Whenever you need to refresh, just go to this new Summary sheet and Choose DataConsolidateOK as all the ranges will have been remembered. -- Regards Roger Govier "MGC" wrote in message ... Dave: Along these same lines: I have several worksheets which I need to combine into a summary page on the first tab. The workbook contans several worksheets (one for each day a job is worked) with the hours for each person. One sheet (day) may contain data for up to 15 guys. I need to be able to extract the date (B6) , class (GF, F, JW, AP) (C9:C44) and the number of hours of straight time, overtime and double time (the letters ST, OT and DT are in column E but the actual hours are in column F) for each individual class onto the summary page. The goal here is to be able to track how much I have remaining on a purchase order. My timesheets cannot be modified, a pivot table does not provide a way that I can find to do this easily and macros are frowned upon. I currently have a macro in use on this workbook that will create a new sheet via the duplication of the previous sheet but this may have to be deleted due to the company not liking macros created by an outside source. Can my dilemma be solved? If I have you confused, I could send you a sample of the workbook to your personal address if needed. Thank you for your time! "Dave Peterson" wrote: Create two new worksheets--one to the far right and one to the far left. Call them Start and End Then using a sheet (Summary) that is outside this "sandwich" of worksheets: =sum(start:end!B37) Then you can drag sheets in and out of that sandwich to play what if games. I'd put a couple of notes on each of these sheets: "don't delete this sheet!" And protect the worksheet so that people don't use it for real data. ACM wrote: I have a workbook with 12 worksheets and I need to sum 1 cell (ex. B37) on each worksheet on one summary page. How can I track the cell from the different worksheets to add them? -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summary page for 12 worksheets
Dave/Roger:
Sorry for the lateness of my reply. If I wanted to run this with my current macro, could I? and what would I need to do to be able to do this? The following is the macro currently in use: Sub DuplicateCurrentSheet() ' 'Copies the currently selected sheet 'placing the copy at the 'end' of the workbook Worksheets(ActiveSheet.Name).Copy after:=Sheets(Worksheets.Count) 'remains on the new sheet End Sub How do you convince your company that the macro doesn't contain a virus...that's the reason I may need to stop using it. Thanks for all the help you guys!!! "Dave Peterson" wrote: I would use a macro--so that won't work for you. You may want to post in the .worksheet.functions newsgroup. Maybe there's some giant formulas you could use (way beyond me!). MGC wrote: Dave: Along these same lines: I have several worksheets which I need to combine into a summary page on the first tab. The workbook contans several worksheets (one for each day a job is worked) with the hours for each person. One sheet (day) may contain data for up to 15 guys. I need to be able to extract the date (B6) , class (GF, F, JW, AP) (C9:C44) and the number of hours of straight time, overtime and double time (the letters ST, OT and DT are in column E but the actual hours are in column F) for each individual class onto the summary page. The goal here is to be able to track how much I have remaining on a purchase order. My timesheets cannot be modified, a pivot table does not provide a way that I can find to do this easily and macros are frowned upon. I currently have a macro in use on this workbook that will create a new sheet via the duplication of the previous sheet but this may have to be deleted due to the company not liking macros created by an outside source. Can my dilemma be solved? If I have you confused, I could send you a sample of the workbook to your personal address if needed. Thank you for your time! "Dave Peterson" wrote: Create two new worksheets--one to the far right and one to the far left. Call them Start and End Then using a sheet (Summary) that is outside this "sandwich" of worksheets: =sum(start:end!B37) Then you can drag sheets in and out of that sandwich to play what if games. I'd put a couple of notes on each of these sheets: "don't delete this sheet!" And protect the worksheet so that people don't use it for real data. ACM wrote: I have a workbook with 12 worksheets and I need to sum 1 cell (ex. B37) on each worksheet on one summary page. How can I track the cell from the different worksheets to add them? -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summary page for 12 worksheets
Most of the original thread has aged off for me. But there's nothing in your
code that precludes running that on any "normal" macro (that I can see, anyway). But I'd use: ActiveSheet.Copy after:=Sheets(sheets.Count) instead of: Worksheets(ActiveSheet.Name).Copy after:=Sheets(Worksheets.Count) MGC wrote: Dave/Roger: Sorry for the lateness of my reply. If I wanted to run this with my current macro, could I? and what would I need to do to be able to do this? The following is the macro currently in use: Sub DuplicateCurrentSheet() ' 'Copies the currently selected sheet 'placing the copy at the 'end' of the workbook Worksheets(ActiveSheet.Name).Copy after:=Sheets(Worksheets.Count) 'remains on the new sheet End Sub How do you convince your company that the macro doesn't contain a virus...that's the reason I may need to stop using it. Thanks for all the help you guys!!! "Dave Peterson" wrote: I would use a macro--so that won't work for you. You may want to post in the .worksheet.functions newsgroup. Maybe there's some giant formulas you could use (way beyond me!). MGC wrote: Dave: Along these same lines: I have several worksheets which I need to combine into a summary page on the first tab. The workbook contans several worksheets (one for each day a job is worked) with the hours for each person. One sheet (day) may contain data for up to 15 guys. I need to be able to extract the date (B6) , class (GF, F, JW, AP) (C9:C44) and the number of hours of straight time, overtime and double time (the letters ST, OT and DT are in column E but the actual hours are in column F) for each individual class onto the summary page. The goal here is to be able to track how much I have remaining on a purchase order. My timesheets cannot be modified, a pivot table does not provide a way that I can find to do this easily and macros are frowned upon. I currently have a macro in use on this workbook that will create a new sheet via the duplication of the previous sheet but this may have to be deleted due to the company not liking macros created by an outside source. Can my dilemma be solved? If I have you confused, I could send you a sample of the workbook to your personal address if needed. Thank you for your time! "Dave Peterson" wrote: Create two new worksheets--one to the far right and one to the far left. Call them Start and End Then using a sheet (Summary) that is outside this "sandwich" of worksheets: =sum(start:end!B37) Then you can drag sheets in and out of that sandwich to play what if games. I'd put a couple of notes on each of these sheets: "don't delete this sheet!" And protect the worksheet so that people don't use it for real data. ACM wrote: I have a workbook with 12 worksheets and I need to sum 1 cell (ex. B37) on each worksheet on one summary page. How can I track the cell from the different worksheets to add them? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summary page for 12 worksheets
Dave:
This is my original post (and the one I am looking for a macro for and how to set up). Please let me know if you can help. Dave: Along these same lines: I have several worksheets which I need to combine into a summary page on the first tab. The workbook contans several worksheets (one for each day a job is worked) with the hours for each person. One sheet (day) may contain data for up to 15 guys. I need to be able to extract the date (B6) , class (GF, F, JW, AP) (C9:C44) and the number of hours of straight time, overtime and double time (the letters ST, OT and DT are in column E but the actual hours are in column F) for each individual class onto the summary page. The goal here is to be able to track how much I have remaining on a purchase order. My timesheets cannot be modified, a pivot table does not provide a way that I can find to do this easily and macros are frowned upon. I currently have a macro in use on this workbook that will create a new sheet via the duplication of the previous sheet but this may have to be deleted due to the company not liking macros created by an outside source. Can my dilemma be solved? If I have you confused, I could send you a sample of the workbook to your personal address if needed. Thank you for your time! "Dave Peterson" wrote: Most of the original thread has aged off for me. But there's nothing in your code that precludes running that on any "normal" macro (that I can see, anyway). But I'd use: ActiveSheet.Copy after:=Sheets(sheets.Count) instead of: Worksheets(ActiveSheet.Name).Copy after:=Sheets(Worksheets.Count) MGC wrote: Dave/Roger: Sorry for the lateness of my reply. If I wanted to run this with my current macro, could I? and what would I need to do to be able to do this? The following is the macro currently in use: Sub DuplicateCurrentSheet() ' 'Copies the currently selected sheet 'placing the copy at the 'end' of the workbook Worksheets(ActiveSheet.Name).Copy after:=Sheets(Worksheets.Count) 'remains on the new sheet End Sub How do you convince your company that the macro doesn't contain a virus...that's the reason I may need to stop using it. Thanks for all the help you guys!!! "Dave Peterson" wrote: I would use a macro--so that won't work for you. You may want to post in the .worksheet.functions newsgroup. Maybe there's some giant formulas you could use (way beyond me!). MGC wrote: Dave: Along these same lines: I have several worksheets which I need to combine into a summary page on the first tab. The workbook contans several worksheets (one for each day a job is worked) with the hours for each person. One sheet (day) may contain data for up to 15 guys. I need to be able to extract the date (B6) , class (GF, F, JW, AP) (C9:C44) and the number of hours of straight time, overtime and double time (the letters ST, OT and DT are in column E but the actual hours are in column F) for each individual class onto the summary page. The goal here is to be able to track how much I have remaining on a purchase order. My timesheets cannot be modified, a pivot table does not provide a way that I can find to do this easily and macros are frowned upon. I currently have a macro in use on this workbook that will create a new sheet via the duplication of the previous sheet but this may have to be deleted due to the company not liking macros created by an outside source. Can my dilemma be solved? If I have you confused, I could send you a sample of the workbook to your personal address if needed. Thank you for your time! "Dave Peterson" wrote: Create two new worksheets--one to the far right and one to the far left. Call them Start and End Then using a sheet (Summary) that is outside this "sandwich" of worksheets: =sum(start:end!B37) Then you can drag sheets in and out of that sandwich to play what if games. I'd put a couple of notes on each of these sheets: "don't delete this sheet!" And protect the worksheet so that people don't use it for real data. ACM wrote: I have a workbook with 12 worksheets and I need to sum 1 cell (ex. B37) on each worksheet on one summary page. How can I track the cell from the different worksheets to add them? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summary page for 12 worksheets
Maybe...
This deletes the worksheet named summary and recreates it. Option Explicit Sub testme02() Dim wks As Worksheet Dim SumWks As Worksheet Dim DestCell As Range Dim myClasses As Variant Dim HowManyClasses As Long Dim myHourTypes As Variant Dim HowManyHourTypes As Long Dim HowManyRows As Long Dim iCtr As Long Dim myFormula As String 'remove the Summary worksheet On Error Resume Next Application.DisplayAlerts = False Worksheets("Summary").Delete Application.DisplayAlerts = True On Error GoTo 0 myClasses = Array("GF", "F", "JW", "AP") HowManyClasses = UBound(myClasses) - LBound(myClasses) + 1 myHourTypes = Array("ST", "OT", "DT") HowManyHourTypes = UBound(myHourTypes) - LBound(myHourTypes) + 1 HowManyRows = HowManyClasses * HowManyHourTypes Set SumWks = Worksheets.Add With SumWks .Name = "Summary" .Range("A1").Resize(1, 5).Value _ = Array("WksName", "Date", "Key", "Hr Type", "hours") Set DestCell = .Range("a2") End With For Each wks In ThisWorkbook.Worksheets Select Case wks.Name Case Is = SumWks.Name 'do nothing Case Else 'put the worksheet name in column A DestCell.Resize(HowManyRows, 1).Value = "'" & wks.Name 'put the date in column B With DestCell.Offset(0, 1).Resize(HowManyRows, 1) .Value = wks.Range("b6").Value .NumberFormat = "mm/dd/yyyy" End With 'put the formula to determine hours in column E myFormula _ = "=SUMPRODUCT(--('" & wks.Name & "'!R9C3:R44C3=RC3)," _ & "--('" & wks.Name & "'!R9C5:R44C5=RC4)," _ & "'" & wks.Name & "'!R9C6:R44C6)" myFormula = Replace(myFormula, "@", Chr(34)) DestCell.Offset(0, 4).Resize(HowManyRows, 1).FormulaR1C1 _ = myFormula 'put the key and hour types options in C:D For iCtr = LBound(myHourTypes) To UBound(myHourTypes) DestCell.Offset(0, 2).Resize(HowManyClasses, 1).Value _ = Application.Transpose(myClasses) DestCell.Offset(0, 3).Resize(HowManyClasses, 1).Value _ = Application.Transpose(myHourTypes(iCtr)) Set DestCell = DestCell.Offset(HowManyClasses, 0) Next iCtr End Select Next wks End Sub I like to know where the data comes from. I put the worksheet name in column A. The date in column B. The class type in column C. The hour type in D and the total hours in column E. In R1C1 reference style, this: R9C3:R44C3 is row 9, column 3 through row 44, column 3 (C9:C44 in A1 reference style) And RC3 means the same row column 3 (C### in A1 reference style) After the data is laid out like this, you could use a pivottable to see nice summaries. MGC wrote: Dave: This is my original post (and the one I am looking for a macro for and how to set up). Please let me know if you can help. Dave: Along these same lines: I have several worksheets which I need to combine into a summary page on the first tab. The workbook contans several worksheets (one for each day a job is worked) with the hours for each person. One sheet (day) may contain data for up to 15 guys. I need to be able to extract the date (B6) , class (GF, F, JW, AP) (C9:C44) and the number of hours of straight time, overtime and double time (the letters ST, OT and DT are in column E but the actual hours are in column F) for each individual class onto the summary page. The goal here is to be able to track how much I have remaining on a purchase order. My timesheets cannot be modified, a pivot table does not provide a way that I can find to do this easily and macros are frowned upon. I currently have a macro in use on this workbook that will create a new sheet via the duplication of the previous sheet but this may have to be deleted due to the company not liking macros created by an outside source. Can my dilemma be solved? If I have you confused, I could send you a sample of the workbook to your personal address if needed. Thank you for your time! "Dave Peterson" wrote: Most of the original thread has aged off for me. But there's nothing in your code that precludes running that on any "normal" macro (that I can see, anyway). But I'd use: ActiveSheet.Copy after:=Sheets(sheets.Count) instead of: Worksheets(ActiveSheet.Name).Copy after:=Sheets(Worksheets.Count) MGC wrote: Dave/Roger: Sorry for the lateness of my reply. If I wanted to run this with my current macro, could I? and what would I need to do to be able to do this? The following is the macro currently in use: Sub DuplicateCurrentSheet() ' 'Copies the currently selected sheet 'placing the copy at the 'end' of the workbook Worksheets(ActiveSheet.Name).Copy after:=Sheets(Worksheets.Count) 'remains on the new sheet End Sub How do you convince your company that the macro doesn't contain a virus...that's the reason I may need to stop using it. Thanks for all the help you guys!!! "Dave Peterson" wrote: I would use a macro--so that won't work for you. You may want to post in the .worksheet.functions newsgroup. Maybe there's some giant formulas you could use (way beyond me!). MGC wrote: Dave: Along these same lines: I have several worksheets which I need to combine into a summary page on the first tab. The workbook contans several worksheets (one for each day a job is worked) with the hours for each person. One sheet (day) may contain data for up to 15 guys. I need to be able to extract the date (B6) , class (GF, F, JW, AP) (C9:C44) and the number of hours of straight time, overtime and double time (the letters ST, OT and DT are in column E but the actual hours are in column F) for each individual class onto the summary page. The goal here is to be able to track how much I have remaining on a purchase order. My timesheets cannot be modified, a pivot table does not provide a way that I can find to do this easily and macros are frowned upon. I currently have a macro in use on this workbook that will create a new sheet via the duplication of the previous sheet but this may have to be deleted due to the company not liking macros created by an outside source. Can my dilemma be solved? If I have you confused, I could send you a sample of the workbook to your personal address if needed. Thank you for your time! "Dave Peterson" wrote: Create two new worksheets--one to the far right and one to the far left. Call them Start and End Then using a sheet (Summary) that is outside this "sandwich" of worksheets: =sum(start:end!B37) Then you can drag sheets in and out of that sandwich to play what if games. I'd put a couple of notes on each of these sheets: "don't delete this sheet!" And protect the worksheet so that people don't use it for real data. ACM wrote: I have a workbook with 12 worksheets and I need to sum 1 cell (ex. B37) on each worksheet on one summary page. How can I track the cell from the different worksheets to add them? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summary page for 12 worksheets
You could delete this line, too:
myFormula = Replace(myFormula, "@", Chr(34)) And some notes about the =sumproduct() formula... Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Dave Peterson wrote: Maybe... This deletes the worksheet named summary and recreates it. Option Explicit Sub testme02() Dim wks As Worksheet Dim SumWks As Worksheet Dim DestCell As Range Dim myClasses As Variant Dim HowManyClasses As Long Dim myHourTypes As Variant Dim HowManyHourTypes As Long Dim HowManyRows As Long Dim iCtr As Long Dim myFormula As String 'remove the Summary worksheet On Error Resume Next Application.DisplayAlerts = False Worksheets("Summary").Delete Application.DisplayAlerts = True On Error GoTo 0 myClasses = Array("GF", "F", "JW", "AP") HowManyClasses = UBound(myClasses) - LBound(myClasses) + 1 myHourTypes = Array("ST", "OT", "DT") HowManyHourTypes = UBound(myHourTypes) - LBound(myHourTypes) + 1 HowManyRows = HowManyClasses * HowManyHourTypes Set SumWks = Worksheets.Add With SumWks .Name = "Summary" .Range("A1").Resize(1, 5).Value _ = Array("WksName", "Date", "Key", "Hr Type", "hours") Set DestCell = .Range("a2") End With For Each wks In ThisWorkbook.Worksheets Select Case wks.Name Case Is = SumWks.Name 'do nothing Case Else 'put the worksheet name in column A DestCell.Resize(HowManyRows, 1).Value = "'" & wks.Name 'put the date in column B With DestCell.Offset(0, 1).Resize(HowManyRows, 1) .Value = wks.Range("b6").Value .NumberFormat = "mm/dd/yyyy" End With 'put the formula to determine hours in column E myFormula _ = "=SUMPRODUCT(--('" & wks.Name & "'!R9C3:R44C3=RC3)," _ & "--('" & wks.Name & "'!R9C5:R44C5=RC4)," _ & "'" & wks.Name & "'!R9C6:R44C6)" myFormula = Replace(myFormula, "@", Chr(34)) DestCell.Offset(0, 4).Resize(HowManyRows, 1).FormulaR1C1 _ = myFormula 'put the key and hour types options in C:D For iCtr = LBound(myHourTypes) To UBound(myHourTypes) DestCell.Offset(0, 2).Resize(HowManyClasses, 1).Value _ = Application.Transpose(myClasses) DestCell.Offset(0, 3).Resize(HowManyClasses, 1).Value _ = Application.Transpose(myHourTypes(iCtr)) Set DestCell = DestCell.Offset(HowManyClasses, 0) Next iCtr End Select Next wks End Sub I like to know where the data comes from. I put the worksheet name in column A. The date in column B. The class type in column C. The hour type in D and the total hours in column E. In R1C1 reference style, this: R9C3:R44C3 is row 9, column 3 through row 44, column 3 (C9:C44 in A1 reference style) And RC3 means the same row column 3 (C### in A1 reference style) After the data is laid out like this, you could use a pivottable to see nice summaries. MGC wrote: Dave: This is my original post (and the one I am looking for a macro for and how to set up). Please let me know if you can help. Dave: Along these same lines: I have several worksheets which I need to combine into a summary page on the first tab. The workbook contans several worksheets (one for each day a job is worked) with the hours for each person. One sheet (day) may contain data for up to 15 guys. I need to be able to extract the date (B6) , class (GF, F, JW, AP) (C9:C44) and the number of hours of straight time, overtime and double time (the letters ST, OT and DT are in column E but the actual hours are in column F) for each individual class onto the summary page. The goal here is to be able to track how much I have remaining on a purchase order. My timesheets cannot be modified, a pivot table does not provide a way that I can find to do this easily and macros are frowned upon. I currently have a macro in use on this workbook that will create a new sheet via the duplication of the previous sheet but this may have to be deleted due to the company not liking macros created by an outside source. Can my dilemma be solved? If I have you confused, I could send you a sample of the workbook to your personal address if needed. Thank you for your time! "Dave Peterson" wrote: Most of the original thread has aged off for me. But there's nothing in your code that precludes running that on any "normal" macro (that I can see, anyway). But I'd use: ActiveSheet.Copy after:=Sheets(sheets.Count) instead of: Worksheets(ActiveSheet.Name).Copy after:=Sheets(Worksheets.Count) MGC wrote: Dave/Roger: Sorry for the lateness of my reply. If I wanted to run this with my current macro, could I? and what would I need to do to be able to do this? The following is the macro currently in use: Sub DuplicateCurrentSheet() ' 'Copies the currently selected sheet 'placing the copy at the 'end' of the workbook Worksheets(ActiveSheet.Name).Copy after:=Sheets(Worksheets.Count) 'remains on the new sheet End Sub How do you convince your company that the macro doesn't contain a virus...that's the reason I may need to stop using it. Thanks for all the help you guys!!! "Dave Peterson" wrote: I would use a macro--so that won't work for you. You may want to post in the .worksheet.functions newsgroup. Maybe there's some giant formulas you could use (way beyond me!). MGC wrote: Dave: Along these same lines: I have several worksheets which I need to combine into a summary page on the first tab. The workbook contans several worksheets (one for each day a job is worked) with the hours for each person. One sheet (day) may contain data for up to 15 guys. I need to be able to extract the date (B6) , class (GF, F, JW, AP) (C9:C44) and the number of hours of straight time, overtime and double time (the letters ST, OT and DT are in column E but the actual hours are in column F) for each individual class onto the summary page. The goal here is to be able to track how much I have remaining on a purchase order. My timesheets cannot be modified, a pivot table does not provide a way that I can find to do this easily and macros are frowned upon. I currently have a macro in use on this workbook that will create a new sheet via the duplication of the previous sheet but this may have to be deleted due to the company not liking macros created by an outside source. Can my dilemma be solved? If I have you confused, I could send you a sample of the workbook to your personal address if needed. Thank you for your time! "Dave Peterson" wrote: Create two new worksheets--one to the far right and one to the far left. Call them Start and End Then using a sheet (Summary) that is outside this "sandwich" of worksheets: =sum(start:end!B37) Then you can drag sheets in and out of that sandwich to play what if games. I'd put a couple of notes on each of these sheets: "don't delete this sheet!" And protect the worksheet so that people don't use it for real data. ACM wrote: I have a workbook with 12 worksheets and I need to sum 1 cell (ex. B37) on each worksheet on one summary page. How can I track the cell from the different worksheets to add them? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summary page for 12 worksheets
Thanks, Dave! I will definitely try this and see what happens. Can I add
this to my already existing macro or does this have to be a new one? "Dave Peterson" wrote: You could delete this line, too: myFormula = Replace(myFormula, "@", Chr(34)) And some notes about the =sumproduct() formula... Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Dave Peterson wrote: Maybe... This deletes the worksheet named summary and recreates it. Option Explicit Sub testme02() Dim wks As Worksheet Dim SumWks As Worksheet Dim DestCell As Range Dim myClasses As Variant Dim HowManyClasses As Long Dim myHourTypes As Variant Dim HowManyHourTypes As Long Dim HowManyRows As Long Dim iCtr As Long Dim myFormula As String 'remove the Summary worksheet On Error Resume Next Application.DisplayAlerts = False Worksheets("Summary").Delete Application.DisplayAlerts = True On Error GoTo 0 myClasses = Array("GF", "F", "JW", "AP") HowManyClasses = UBound(myClasses) - LBound(myClasses) + 1 myHourTypes = Array("ST", "OT", "DT") HowManyHourTypes = UBound(myHourTypes) - LBound(myHourTypes) + 1 HowManyRows = HowManyClasses * HowManyHourTypes Set SumWks = Worksheets.Add With SumWks .Name = "Summary" .Range("A1").Resize(1, 5).Value _ = Array("WksName", "Date", "Key", "Hr Type", "hours") Set DestCell = .Range("a2") End With For Each wks In ThisWorkbook.Worksheets Select Case wks.Name Case Is = SumWks.Name 'do nothing Case Else 'put the worksheet name in column A DestCell.Resize(HowManyRows, 1).Value = "'" & wks.Name 'put the date in column B With DestCell.Offset(0, 1).Resize(HowManyRows, 1) .Value = wks.Range("b6").Value .NumberFormat = "mm/dd/yyyy" End With 'put the formula to determine hours in column E myFormula _ = "=SUMPRODUCT(--('" & wks.Name & "'!R9C3:R44C3=RC3)," _ & "--('" & wks.Name & "'!R9C5:R44C5=RC4)," _ & "'" & wks.Name & "'!R9C6:R44C6)" myFormula = Replace(myFormula, "@", Chr(34)) DestCell.Offset(0, 4).Resize(HowManyRows, 1).FormulaR1C1 _ = myFormula 'put the key and hour types options in C:D For iCtr = LBound(myHourTypes) To UBound(myHourTypes) DestCell.Offset(0, 2).Resize(HowManyClasses, 1).Value _ = Application.Transpose(myClasses) DestCell.Offset(0, 3).Resize(HowManyClasses, 1).Value _ = Application.Transpose(myHourTypes(iCtr)) Set DestCell = DestCell.Offset(HowManyClasses, 0) Next iCtr End Select Next wks End Sub I like to know where the data comes from. I put the worksheet name in column A. The date in column B. The class type in column C. The hour type in D and the total hours in column E. In R1C1 reference style, this: R9C3:R44C3 is row 9, column 3 through row 44, column 3 (C9:C44 in A1 reference style) And RC3 means the same row column 3 (C### in A1 reference style) After the data is laid out like this, you could use a pivottable to see nice summaries. MGC wrote: Dave: This is my original post (and the one I am looking for a macro for and how to set up). Please let me know if you can help. Dave: Along these same lines: I have several worksheets which I need to combine into a summary page on the first tab. The workbook contans several worksheets (one for each day a job is worked) with the hours for each person. One sheet (day) may contain data for up to 15 guys. I need to be able to extract the date (B6) , class (GF, F, JW, AP) (C9:C44) and the number of hours of straight time, overtime and double time (the letters ST, OT and DT are in column E but the actual hours are in column F) for each individual class onto the summary page. The goal here is to be able to track how much I have remaining on a purchase order. My timesheets cannot be modified, a pivot table does not provide a way that I can find to do this easily and macros are frowned upon. I currently have a macro in use on this workbook that will create a new sheet via the duplication of the previous sheet but this may have to be deleted due to the company not liking macros created by an outside source. Can my dilemma be solved? If I have you confused, I could send you a sample of the workbook to your personal address if needed. Thank you for your time! "Dave Peterson" wrote: Most of the original thread has aged off for me. But there's nothing in your code that precludes running that on any "normal" macro (that I can see, anyway). But I'd use: ActiveSheet.Copy after:=Sheets(sheets.Count) instead of: Worksheets(ActiveSheet.Name).Copy after:=Sheets(Worksheets.Count) MGC wrote: Dave/Roger: Sorry for the lateness of my reply. If I wanted to run this with my current macro, could I? and what would I need to do to be able to do this? The following is the macro currently in use: Sub DuplicateCurrentSheet() ' 'Copies the currently selected sheet 'placing the copy at the 'end' of the workbook Worksheets(ActiveSheet.Name).Copy after:=Sheets(Worksheets.Count) 'remains on the new sheet End Sub How do you convince your company that the macro doesn't contain a virus...that's the reason I may need to stop using it. Thanks for all the help you guys!!! "Dave Peterson" wrote: I would use a macro--so that won't work for you. You may want to post in the .worksheet.functions newsgroup. Maybe there's some giant formulas you could use (way beyond me!). MGC wrote: Dave: Along these same lines: I have several worksheets which I need to combine into a summary page on the first tab. The workbook contans several worksheets (one for each day a job is worked) with the hours for each person. One sheet (day) may contain data for up to 15 guys. I need to be able to extract the date (B6) , class (GF, F, JW, AP) (C9:C44) and the number of hours of straight time, overtime and double time (the letters ST, OT and DT are in column E but the actual hours are in column F) for each individual class onto the summary page. The goal here is to be able to track how much I have remaining on a purchase order. My timesheets cannot be modified, a pivot table does not provide a way that I can find to do this easily and macros are frowned upon. I currently have a macro in use on this workbook that will create a new sheet via the duplication of the previous sheet but this may have to be deleted due to the company not liking macros created by an outside source. Can my dilemma be solved? If I have you confused, I could send you a sample of the workbook to your personal address if needed. Thank you for your time! "Dave Peterson" wrote: Create two new worksheets--one to the far right and one to the far left. Call them Start and End Then using a sheet (Summary) that is outside this "sandwich" of worksheets: =sum(start:end!B37) Then you can drag sheets in and out of that sandwich to play what if games. I'd put a couple of notes on each of these sheets: "don't delete this sheet!" And protect the worksheet so that people don't use it for real data. ACM wrote: I have a workbook with 12 worksheets and I need to sum 1 cell (ex. B37) on each worksheet on one summary page. How can I track the cell from the different worksheets to add them? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summary page for 12 worksheets
I'd use a new macro until you got it working.
But I still don't know what your existing macro does. You can give this a nice name and then just call it with a line like: Call SummarizeMySheets MGC wrote: Thanks, Dave! I will definitely try this and see what happens. Can I add this to my already existing macro or does this have to be a new one? "Dave Peterson" wrote: You could delete this line, too: myFormula = Replace(myFormula, "@", Chr(34)) And some notes about the =sumproduct() formula... Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Dave Peterson wrote: Maybe... This deletes the worksheet named summary and recreates it. Option Explicit Sub testme02() Dim wks As Worksheet Dim SumWks As Worksheet Dim DestCell As Range Dim myClasses As Variant Dim HowManyClasses As Long Dim myHourTypes As Variant Dim HowManyHourTypes As Long Dim HowManyRows As Long Dim iCtr As Long Dim myFormula As String 'remove the Summary worksheet On Error Resume Next Application.DisplayAlerts = False Worksheets("Summary").Delete Application.DisplayAlerts = True On Error GoTo 0 myClasses = Array("GF", "F", "JW", "AP") HowManyClasses = UBound(myClasses) - LBound(myClasses) + 1 myHourTypes = Array("ST", "OT", "DT") HowManyHourTypes = UBound(myHourTypes) - LBound(myHourTypes) + 1 HowManyRows = HowManyClasses * HowManyHourTypes Set SumWks = Worksheets.Add With SumWks .Name = "Summary" .Range("A1").Resize(1, 5).Value _ = Array("WksName", "Date", "Key", "Hr Type", "hours") Set DestCell = .Range("a2") End With For Each wks In ThisWorkbook.Worksheets Select Case wks.Name Case Is = SumWks.Name 'do nothing Case Else 'put the worksheet name in column A DestCell.Resize(HowManyRows, 1).Value = "'" & wks.Name 'put the date in column B With DestCell.Offset(0, 1).Resize(HowManyRows, 1) .Value = wks.Range("b6").Value .NumberFormat = "mm/dd/yyyy" End With 'put the formula to determine hours in column E myFormula _ = "=SUMPRODUCT(--('" & wks.Name & "'!R9C3:R44C3=RC3)," _ & "--('" & wks.Name & "'!R9C5:R44C5=RC4)," _ & "'" & wks.Name & "'!R9C6:R44C6)" myFormula = Replace(myFormula, "@", Chr(34)) DestCell.Offset(0, 4).Resize(HowManyRows, 1).FormulaR1C1 _ = myFormula 'put the key and hour types options in C:D For iCtr = LBound(myHourTypes) To UBound(myHourTypes) DestCell.Offset(0, 2).Resize(HowManyClasses, 1).Value _ = Application.Transpose(myClasses) DestCell.Offset(0, 3).Resize(HowManyClasses, 1).Value _ = Application.Transpose(myHourTypes(iCtr)) Set DestCell = DestCell.Offset(HowManyClasses, 0) Next iCtr End Select Next wks End Sub I like to know where the data comes from. I put the worksheet name in column A. The date in column B. The class type in column C. The hour type in D and the total hours in column E. In R1C1 reference style, this: R9C3:R44C3 is row 9, column 3 through row 44, column 3 (C9:C44 in A1 reference style) And RC3 means the same row column 3 (C### in A1 reference style) After the data is laid out like this, you could use a pivottable to see nice summaries. MGC wrote: Dave: This is my original post (and the one I am looking for a macro for and how to set up). Please let me know if you can help. Dave: Along these same lines: I have several worksheets which I need to combine into a summary page on the first tab. The workbook contans several worksheets (one for each day a job is worked) with the hours for each person. One sheet (day) may contain data for up to 15 guys. I need to be able to extract the date (B6) , class (GF, F, JW, AP) (C9:C44) and the number of hours of straight time, overtime and double time (the letters ST, OT and DT are in column E but the actual hours are in column F) for each individual class onto the summary page. The goal here is to be able to track how much I have remaining on a purchase order. My timesheets cannot be modified, a pivot table does not provide a way that I can find to do this easily and macros are frowned upon. I currently have a macro in use on this workbook that will create a new sheet via the duplication of the previous sheet but this may have to be deleted due to the company not liking macros created by an outside source. Can my dilemma be solved? If I have you confused, I could send you a sample of the workbook to your personal address if needed. Thank you for your time! "Dave Peterson" wrote: Most of the original thread has aged off for me. But there's nothing in your code that precludes running that on any "normal" macro (that I can see, anyway). But I'd use: ActiveSheet.Copy after:=Sheets(sheets.Count) instead of: Worksheets(ActiveSheet.Name).Copy after:=Sheets(Worksheets.Count) MGC wrote: Dave/Roger: Sorry for the lateness of my reply. If I wanted to run this with my current macro, could I? and what would I need to do to be able to do this? The following is the macro currently in use: Sub DuplicateCurrentSheet() ' 'Copies the currently selected sheet 'placing the copy at the 'end' of the workbook Worksheets(ActiveSheet.Name).Copy after:=Sheets(Worksheets.Count) 'remains on the new sheet End Sub How do you convince your company that the macro doesn't contain a virus...that's the reason I may need to stop using it. Thanks for all the help you guys!!! "Dave Peterson" wrote: I would use a macro--so that won't work for you. You may want to post in the .worksheet.functions newsgroup. Maybe there's some giant formulas you could use (way beyond me!). MGC wrote: Dave: Along these same lines: I have several worksheets which I need to combine into a summary page on the first tab. The workbook contans several worksheets (one for each day a job is worked) with the hours for each person. One sheet (day) may contain data for up to 15 guys. I need to be able to extract the date (B6) , class (GF, F, JW, AP) (C9:C44) and the number of hours of straight time, overtime and double time (the letters ST, OT and DT are in column E but the actual hours are in column F) for each individual class onto the summary page. The goal here is to be able to track how much I have remaining on a purchase order. My timesheets cannot be modified, a pivot table does not provide a way that I can find to do this easily and macros are frowned upon. I currently have a macro in use on this workbook that will create a new sheet via the duplication of the previous sheet but this may have to be deleted due to the company not liking macros created by an outside source. Can my dilemma be solved? If I have you confused, I could send you a sample of the workbook to your personal address if needed. Thank you for your time! "Dave Peterson" wrote: Create two new worksheets--one to the far right and one to the far left. Call them Start and End Then using a sheet (Summary) that is outside this "sandwich" of worksheets: =sum(start:end!B37) Then you can drag sheets in and out of that sandwich to play what if games. I'd put a couple of notes on each of these sheets: "don't delete this sheet!" And protect the worksheet so that people don't use it for real data. ACM wrote: I have a workbook with 12 worksheets and I need to sum 1 cell (ex. B37) on each worksheet on one summary page. How can I track the cell from the different worksheets to add them? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I uniformly scale all worksheets at once in page set-up in on. | Setting up and Configuration of Excel | |||
need help with formula on summary page | Excel Discussion (Misc queries) | |||
Spawning worksheets and a summary per worksheet | Excel Discussion (Misc queries) | |||
How do I reference values from 200 worksheets onto a summary sheet | Excel Discussion (Misc queries) | |||
Auto page numbering for several worksheets | Excel Worksheet Functions |