Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible?
I have a workbook where the first worksheet (Sheet1) is kind of a template.
The user enters data there and some calculations occur and determine how many hours should be coded for an employee for sick time, vacation, holiday and short term disability etc. What I am trying to have happen is: User enters data on Sheet1 and hours are calculated on that same sheet. A copy of that sheet is made and is named Payroll Period 1. Sheet1 is cleared and ready for next week. Next week the process repeats itself. The tricky part is all those Payroll Period 1, Payroll Period 2 etc. sheets stack up at the bottom of the workbook. I want to have a summary worksheet that adds them up by week. I know how to link together the formulas, but if I do before the worksheet has been created, it References out in the formula on the Summary sheet, and doesn't correct itself once the worksheet is created. I tried creating all the Payroll Period sheets up front (max of 13) and link them to eliminate this error. However, then the copy of Sheet1 needs to go to an already existing worksheet rather than a new copy being made. Anyone follow any of this? Any ideas??? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible?
for a select few worksheet functions, you can have 3D range references. Sum
is one of these. You could put in two permanent blank sheets. Name one First and one Last. Put all your Payroll Period sheets and only those sheets between the first and last sheets (in the tab order). Then in you summary sheet, use formulas like =Sum(First:Last!$B$2:$B$8) when you place more sheets between first and last, they will be included in the sum -- Regards, Tom Ogilvy "TimN" wrote: I have a workbook where the first worksheet (Sheet1) is kind of a template. The user enters data there and some calculations occur and determine how many hours should be coded for an employee for sick time, vacation, holiday and short term disability etc. What I am trying to have happen is: User enters data on Sheet1 and hours are calculated on that same sheet. A copy of that sheet is made and is named Payroll Period 1. Sheet1 is cleared and ready for next week. Next week the process repeats itself. The tricky part is all those Payroll Period 1, Payroll Period 2 etc. sheets stack up at the bottom of the workbook. I want to have a summary worksheet that adds them up by week. I know how to link together the formulas, but if I do before the worksheet has been created, it References out in the formula on the Summary sheet, and doesn't correct itself once the worksheet is created. I tried creating all the Payroll Period sheets up front (max of 13) and link them to eliminate this error. However, then the copy of Sheet1 needs to go to an already existing worksheet rather than a new copy being made. Anyone follow any of this? Any ideas??? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible?
An easy trick is to add two new blank worksheets, call them Payroll_Start and
Payroll_End. On your summary worksheet sum everything between the two new worksheets. When you copy from your template, place the copy before Payroll_End. Hope this helps, -- Les Torchia-Wells "TimN" wrote: I have a workbook where the first worksheet (Sheet1) is kind of a template. The user enters data there and some calculations occur and determine how many hours should be coded for an employee for sick time, vacation, holiday and short term disability etc. What I am trying to have happen is: User enters data on Sheet1 and hours are calculated on that same sheet. A copy of that sheet is made and is named Payroll Period 1. Sheet1 is cleared and ready for next week. Next week the process repeats itself. The tricky part is all those Payroll Period 1, Payroll Period 2 etc. sheets stack up at the bottom of the workbook. I want to have a summary worksheet that adds them up by week. I know how to link together the formulas, but if I do before the worksheet has been created, it References out in the formula on the Summary sheet, and doesn't correct itself once the worksheet is created. I tried creating all the Payroll Period sheets up front (max of 13) and link them to eliminate this error. However, then the copy of Sheet1 needs to go to an already existing worksheet rather than a new copy being made. Anyone follow any of this? Any ideas??? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible?
Thanks, I will give that a try.
One more question. I know this has to be an easy thing. When the new worksheet is created how do I write code to make the original sheet (Sheet1), not the newly created sheet, be the active worksheet at cell A1? "Tom Ogilvy" wrote: for a select few worksheet functions, you can have 3D range references. Sum is one of these. You could put in two permanent blank sheets. Name one First and one Last. Put all your Payroll Period sheets and only those sheets between the first and last sheets (in the tab order). Then in you summary sheet, use formulas like =Sum(First:Last!$B$2:$B$8) when you place more sheets between first and last, they will be included in the sum -- Regards, Tom Ogilvy "TimN" wrote: I have a workbook where the first worksheet (Sheet1) is kind of a template. The user enters data there and some calculations occur and determine how many hours should be coded for an employee for sick time, vacation, holiday and short term disability etc. What I am trying to have happen is: User enters data on Sheet1 and hours are calculated on that same sheet. A copy of that sheet is made and is named Payroll Period 1. Sheet1 is cleared and ready for next week. Next week the process repeats itself. The tricky part is all those Payroll Period 1, Payroll Period 2 etc. sheets stack up at the bottom of the workbook. I want to have a summary worksheet that adds them up by week. I know how to link together the formulas, but if I do before the worksheet has been created, it References out in the formula on the Summary sheet, and doesn't correct itself once the worksheet is created. I tried creating all the Payroll Period sheets up front (max of 13) and link them to eliminate this error. However, then the copy of Sheet1 needs to go to an already existing worksheet rather than a new copy being made. Anyone follow any of this? Any ideas??? Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible?
worksheets("Sheet1").copy Befo=worksheets("End") Activesheet.Name = "Payroll Period " & i worksheets(1).Activate worksheets(1).Range("A1").Select -- Regards, Tom Ogilvy "TimN" wrote: Thanks, I will give that a try. One more question. I know this has to be an easy thing. When the new worksheet is created how do I write code to make the original sheet (Sheet1), not the newly created sheet, be the active worksheet at cell A1? "Tom Ogilvy" wrote: for a select few worksheet functions, you can have 3D range references. Sum is one of these. You could put in two permanent blank sheets. Name one First and one Last. Put all your Payroll Period sheets and only those sheets between the first and last sheets (in the tab order). Then in you summary sheet, use formulas like =Sum(First:Last!$B$2:$B$8) when you place more sheets between first and last, they will be included in the sum -- Regards, Tom Ogilvy "TimN" wrote: I have a workbook where the first worksheet (Sheet1) is kind of a template. The user enters data there and some calculations occur and determine how many hours should be coded for an employee for sick time, vacation, holiday and short term disability etc. What I am trying to have happen is: User enters data on Sheet1 and hours are calculated on that same sheet. A copy of that sheet is made and is named Payroll Period 1. Sheet1 is cleared and ready for next week. Next week the process repeats itself. The tricky part is all those Payroll Period 1, Payroll Period 2 etc. sheets stack up at the bottom of the workbook. I want to have a summary worksheet that adds them up by week. I know how to link together the formulas, but if I do before the worksheet has been created, it References out in the formula on the Summary sheet, and doesn't correct itself once the worksheet is created. I tried creating all the Payroll Period sheets up front (max of 13) and link them to eliminate this error. However, then the copy of Sheet1 needs to go to an already existing worksheet rather than a new copy being made. Anyone follow any of this? Any ideas??? Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible?
Tom,
I get a complie error, Variable not defined and it highlightes the "i" in the code you provided. Below is my full set of code: Private Sub cmdOK_Click() 'MsgBox to indicate "not yet approved" if STD Approved date box is skipped If IsDate(Me.TxtDateApprovedforSTD.Value) Then Worksheets("STD Calc").Range("C4").Value = CDate(Me.TxtDateApprovedforSTD.Value) Else Worksheets("STD Calc").Range("C4").Value = "Not Yet Approved" End If 'creates a duplicate worksheet and new name given by user Do Dim nSheet As Worksheet Dim NameBox As String NameBox = Application.InputBox("Please type a name for the new worksheet", "Creating New Sheet", , , , , , 2) If NameBox = "" Or NameBox = "False" Then MsgBox "Please type a name for the new worksheet" End If Loop Until Not NameBox = "" Or NameBox = "False" Worksheets("STD Calc").Copy Befo=Worksheets("End") ActiveSheet.Name = "Payroll Period" & i Worksheet(1).Activate Worksheet(1).Range("a1").Select Unload Me End Sub "Tom Ogilvy" wrote: worksheets("Sheet1").copy Befo=worksheets("End") Activesheet.Name = "Payroll Period " & i worksheets(1).Activate worksheets(1).Range("A1").Select -- Regards, Tom Ogilvy "TimN" wrote: Thanks, I will give that a try. One more question. I know this has to be an easy thing. When the new worksheet is created how do I write code to make the original sheet (Sheet1), not the newly created sheet, be the active worksheet at cell A1? "Tom Ogilvy" wrote: for a select few worksheet functions, you can have 3D range references. Sum is one of these. You could put in two permanent blank sheets. Name one First and one Last. Put all your Payroll Period sheets and only those sheets between the first and last sheets (in the tab order). Then in you summary sheet, use formulas like =Sum(First:Last!$B$2:$B$8) when you place more sheets between first and last, they will be included in the sum -- Regards, Tom Ogilvy "TimN" wrote: I have a workbook where the first worksheet (Sheet1) is kind of a template. The user enters data there and some calculations occur and determine how many hours should be coded for an employee for sick time, vacation, holiday and short term disability etc. What I am trying to have happen is: User enters data on Sheet1 and hours are calculated on that same sheet. A copy of that sheet is made and is named Payroll Period 1. Sheet1 is cleared and ready for next week. Next week the process repeats itself. The tricky part is all those Payroll Period 1, Payroll Period 2 etc. sheets stack up at the bottom of the workbook. I want to have a summary worksheet that adds them up by week. I know how to link together the formulas, but if I do before the worksheet has been created, it References out in the formula on the Summary sheet, and doesn't correct itself once the worksheet is created. I tried creating all the Payroll Period sheets up front (max of 13) and link them to eliminate this error. However, then the copy of Sheet1 needs to go to an already existing worksheet rather than a new copy being made. Anyone follow any of this? Any ideas??? Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible?
I got ity to work. Thanks for your help!
"TimN" wrote: Tom, I get a complie error, Variable not defined and it highlightes the "i" in the code you provided. Below is my full set of code: Private Sub cmdOK_Click() 'MsgBox to indicate "not yet approved" if STD Approved date box is skipped If IsDate(Me.TxtDateApprovedforSTD.Value) Then Worksheets("STD Calc").Range("C4").Value = CDate(Me.TxtDateApprovedforSTD.Value) Else Worksheets("STD Calc").Range("C4").Value = "Not Yet Approved" End If 'creates a duplicate worksheet and new name given by user Do Dim nSheet As Worksheet Dim NameBox As String NameBox = Application.InputBox("Please type a name for the new worksheet", "Creating New Sheet", , , , , , 2) If NameBox = "" Or NameBox = "False" Then MsgBox "Please type a name for the new worksheet" End If Loop Until Not NameBox = "" Or NameBox = "False" Worksheets("STD Calc").Copy Befo=Worksheets("End") ActiveSheet.Name = "Payroll Period" & i Worksheet(1).Activate Worksheet(1).Range("a1").Select Unload Me End Sub "Tom Ogilvy" wrote: worksheets("Sheet1").copy Befo=worksheets("End") Activesheet.Name = "Payroll Period " & i worksheets(1).Activate worksheets(1).Range("A1").Select -- Regards, Tom Ogilvy "TimN" wrote: Thanks, I will give that a try. One more question. I know this has to be an easy thing. When the new worksheet is created how do I write code to make the original sheet (Sheet1), not the newly created sheet, be the active worksheet at cell A1? "Tom Ogilvy" wrote: for a select few worksheet functions, you can have 3D range references. Sum is one of these. You could put in two permanent blank sheets. Name one First and one Last. Put all your Payroll Period sheets and only those sheets between the first and last sheets (in the tab order). Then in you summary sheet, use formulas like =Sum(First:Last!$B$2:$B$8) when you place more sheets between first and last, they will be included in the sum -- Regards, Tom Ogilvy "TimN" wrote: I have a workbook where the first worksheet (Sheet1) is kind of a template. The user enters data there and some calculations occur and determine how many hours should be coded for an employee for sick time, vacation, holiday and short term disability etc. What I am trying to have happen is: User enters data on Sheet1 and hours are calculated on that same sheet. A copy of that sheet is made and is named Payroll Period 1. Sheet1 is cleared and ready for next week. Next week the process repeats itself. The tricky part is all those Payroll Period 1, Payroll Period 2 etc. sheets stack up at the bottom of the workbook. I want to have a summary worksheet that adds them up by week. I know how to link together the formulas, but if I do before the worksheet has been created, it References out in the formula on the Summary sheet, and doesn't correct itself once the worksheet is created. I tried creating all the Payroll Period sheets up front (max of 13) and link them to eliminate this error. However, then the copy of Sheet1 needs to go to an already existing worksheet rather than a new copy being made. Anyone follow any of this? Any ideas??? Thanks |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible?
Glad you resolved it.
I just put the i in to represent that you would be using sequential names. Sometimes people take these examples too literally. Like if someone asked "how do I travel down column A from row 1 to row 10?" someone might answer Dim cell as Range for each cell in Range("A1:A10") msgbox cell.Address Next putting in the msgbox just to illustrate what the variable cell is and how to reference it since the question included no other functionality. All to often, the OP will come back and say "I don't want to show a msgbox. How do I get rid of the msgbox? " -- Regards, Tom Ogilvy "TimN" wrote: I got ity to work. Thanks for your help! "TimN" wrote: Tom, I get a complie error, Variable not defined and it highlightes the "i" in the code you provided. Below is my full set of code: Private Sub cmdOK_Click() 'MsgBox to indicate "not yet approved" if STD Approved date box is skipped If IsDate(Me.TxtDateApprovedforSTD.Value) Then Worksheets("STD Calc").Range("C4").Value = CDate(Me.TxtDateApprovedforSTD.Value) Else Worksheets("STD Calc").Range("C4").Value = "Not Yet Approved" End If 'creates a duplicate worksheet and new name given by user Do Dim nSheet As Worksheet Dim NameBox As String NameBox = Application.InputBox("Please type a name for the new worksheet", "Creating New Sheet", , , , , , 2) If NameBox = "" Or NameBox = "False" Then MsgBox "Please type a name for the new worksheet" End If Loop Until Not NameBox = "" Or NameBox = "False" Worksheets("STD Calc").Copy Befo=Worksheets("End") ActiveSheet.Name = "Payroll Period" & i Worksheet(1).Activate Worksheet(1).Range("a1").Select Unload Me End Sub "Tom Ogilvy" wrote: worksheets("Sheet1").copy Befo=worksheets("End") Activesheet.Name = "Payroll Period " & i worksheets(1).Activate worksheets(1).Range("A1").Select -- Regards, Tom Ogilvy "TimN" wrote: Thanks, I will give that a try. One more question. I know this has to be an easy thing. When the new worksheet is created how do I write code to make the original sheet (Sheet1), not the newly created sheet, be the active worksheet at cell A1? "Tom Ogilvy" wrote: for a select few worksheet functions, you can have 3D range references. Sum is one of these. You could put in two permanent blank sheets. Name one First and one Last. Put all your Payroll Period sheets and only those sheets between the first and last sheets (in the tab order). Then in you summary sheet, use formulas like =Sum(First:Last!$B$2:$B$8) when you place more sheets between first and last, they will be included in the sum -- Regards, Tom Ogilvy "TimN" wrote: I have a workbook where the first worksheet (Sheet1) is kind of a template. The user enters data there and some calculations occur and determine how many hours should be coded for an employee for sick time, vacation, holiday and short term disability etc. What I am trying to have happen is: User enters data on Sheet1 and hours are calculated on that same sheet. A copy of that sheet is made and is named Payroll Period 1. Sheet1 is cleared and ready for next week. Next week the process repeats itself. The tricky part is all those Payroll Period 1, Payroll Period 2 etc. sheets stack up at the bottom of the workbook. I want to have a summary worksheet that adds them up by week. I know how to link together the formulas, but if I do before the worksheet has been created, it References out in the formula on the Summary sheet, and doesn't correct itself once the worksheet is created. I tried creating all the Payroll Period sheets up front (max of 13) and link them to eliminate this error. However, then the copy of Sheet1 needs to go to an already existing worksheet rather than a new copy being made. Anyone follow any of this? Any ideas??? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|