Is it possible?

[TimN]

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

[Tom Ogilvy]

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
>
>

[Les]

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
>
>

[Tom Ogilvy]

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
>
>

[Les]

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
>
>

[TimN]

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
> >
> >

[Tom Ogilvy]

worksheets("Sheet1").copy Before:=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
> > >
> > >

[TimN]

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 Before:=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 Before:=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
> > > >
> > > >

[TimN]

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 Before:=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 Before:=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
> > > > >
> > > > >

[Tom Ogilvy]

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 Before:=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 Before:=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
> > > > > >
> > > > > >