I keep track of monthly finances with a simple spreadsheet. I keep one sheet
unedited to copy/paste each month into a new sheet (current month-year), then
fill in all the relevant data (dollar amounts/rates/misc fees/etc).

What I would like to have is a button or something that will create a copy
of this original/template, name it (with the current month-year), open it so
it's the currently viewed screen, and have the name of the sheet (current
month-year) in cell A1.

I haven't a clue where to start so any help will be greatly appreciated.

Many thanks,
Ann~

I would hide that template sheet to keep it safely out of the way.

Then I'd add instruction worksheet that describes what needs to be done. And
I'd drop a button from the Forms toolbar onto that sheet and assign it this
macro:

Option Explicit
Sub testme01()
Dim TmpWks As Worksheet
Dim NewWks As Worksheet
Dim myStr As String

myStr = Format(Date, "mmmm-yyyy")

Set NewWks = Nothing
On Error Resume Next
Set NewWks = Worksheets(myStr)
On Error GoTo 0

If NewWks Is Nothing Then
'doesn't exist, keep going
Else
MsgBox "That sheet already exists!"
Exit Sub
End If

Set TmpWks = Worksheets("Template")
With TmpWks
.Visible = xlSheetVisible
.Copy _
after:=Worksheets(Worksheets.Count)
Set NewWks = ActiveSheet
.Visible = xlSheetHidden
End With

With NewWks
.Name = myStr
With .Range("a1")
.Value = Date
.NumberFormat = "mmmm-yyyy"
End With
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ann~ wrote:

I keep track of monthly finances with a simple spreadsheet. I keep one sheet
unedited to copy/paste each month into a new sheet (current month-year), then
fill in all the relevant data (dollar amounts/rates/misc fees/etc).

What I would like to have is a button or something that will create a copy
of this original/template, name it (with the current month-year), open it so
it's the currently viewed screen, and have the name of the sheet (current
month-year) in cell A1.

I haven't a clue where to start so any help will be greatly appreciated.

Many thanks,
Ann~

--

Dave Peterson

Thanks for your help Dave ~ Ive used Excel for years, but never before like
this!

Ive named the original sheet €œtemplate,€� recorded the macro you gave me,
and am totally thrilled with the results! Youve given me confidence that
Ill have this working right shortly. However, if youve got some spare
time, I could use help on some of the basics€¦

Ive gotten the €œindex€� sheet, for lack of a better term, looking fairly
nice. It has general info/instructions as well as the button you told me to
add.

Two things have me stumped€¦

1.) How do I incorporate the macro data that you gave me with the preset
data that showed up when the button was created? (Forgive if this is
stupid.) Ive unsuccessfully tried it below the existing text, in between
the existing text, and in place of the existing text.

2.) How would a formula be written that would show the results of cell C7 of
the most recently created sheet (aka €œcurrent month-year€�)? Id like to
include this on the €œindex€� sheet.

Ann
~*~


"Dave Peterson" wrote:

I would hide that template sheet to keep it safely out of the way.

Then I'd add instruction worksheet that describes what needs to be done. And
I'd drop a button from the Forms toolbar onto that sheet and assign it this
macro:

Option Explicit
Sub testme01()
Dim TmpWks As Worksheet
Dim NewWks As Worksheet
Dim myStr As String

myStr = Format(Date, "mmmm-yyyy")

Set NewWks = Nothing
On Error Resume Next
Set NewWks = Worksheets(myStr)
On Error GoTo 0

If NewWks Is Nothing Then
'doesn't exist, keep going
Else
MsgBox "That sheet already exists!"
Exit Sub
End If

Set TmpWks = Worksheets("Template")
With TmpWks
.Visible = xlSheetVisible
.Copy _
after:=Worksheets(Worksheets.Count)
Set NewWks = ActiveSheet
.Visible = xlSheetHidden
End With

With NewWks
.Name = myStr
With .Range("a1")
.Value = Date
.NumberFormat = "mmmm-yyyy"
End With
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ann~ wrote:

I keep track of monthly finances with a simple spreadsheet. I keep one sheet
unedited to copy/paste each month into a new sheet (current month-year), then
fill in all the relevant data (dollar amounts/rates/misc fees/etc).

What I would like to have is a button or something that will create a copy
of this original/template, name it (with the current month-year), open it so
it's the currently viewed screen, and have the name of the sheet (current
month-year) in cell A1.

I haven't a clue where to start so any help will be greatly appreciated.

Many thanks,
Ann~

--

Dave Peterson

I don't understand the first question.

But the second can be accomplished by adding a bit more to the code.

Option Explicit
Sub testme01()
Dim TmpWks As Worksheet
Dim NewWks As Worksheet
Dim myStr As String
Dim ActSheet As Worksheet
Dim DestCell As Range

Set ActSheet = ActiveSheet

myStr = Format(Date, "mmmm-yyyy")

Set NewWks = Nothing
On Error Resume Next
Set NewWks = Worksheets(myStr)
On Error GoTo 0

If NewWks Is Nothing Then
'doesn't exist, keep going
Else
MsgBox "That sheet already exists!"
Exit Sub
End If

Set TmpWks = Worksheets("Template")
With TmpWks
.Visible = xlSheetVisible
.Copy _
after:=Worksheets(Worksheets.Count)
Set NewWks = ActiveSheet
.Visible = xlSheetHidden
End With

With NewWks
.Name = myStr
With .Range("a1")
.Value = Date
.NumberFormat = "mmmm-yyyy"
End With
End With

'added (along with the declaration statements at the top)
With ActSheet 'the sheet with the button, just to avoid names
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
DestCell.Formula = "='" & NewWks.Name & "'!c7"
End With

End Sub

I put the value of the newsheet's c7 in the next available cell in column A.


==========
Ahhh. I think I understand the first question!

You used a commandbutton from the Control toolbox toolbar. Throw that away
(along with its _click procedure) and replace it with a button from the Forms
toolbar.

And remember this code will go in a General module--not behind the worksheet.

Ann~ wrote:

Thanks for your help Dave ~ Ive used Excel for years, but never before like
this!

Ive named the original sheet €œtemplate,€� recorded the macro you gave me,
and am totally thrilled with the results! Youve given me confidence that
Ill have this working right shortly. However, if youve got some spare
time, I could use help on some of the basics€¦

Ive gotten the €œindex€� sheet, for lack of a better term, looking fairly
nice. It has general info/instructions as well as the button you told me to
add.

Two things have me stumped€¦

1.) How do I incorporate the macro data that you gave me with the preset
data that showed up when the button was created? (Forgive if this is
stupid.) Ive unsuccessfully tried it below the existing text, in between
the existing text, and in place of the existing text.

2.) How would a formula be written that would show the results of cell C7 of
the most recently created sheet (aka €œcurrent month-year€�)? Id like to
include this on the €œindex€� sheet.

Ann
~*~

"Dave Peterson" wrote:

I would hide that template sheet to keep it safely out of the way.

Then I'd add instruction worksheet that describes what needs to be done. And
I'd drop a button from the Forms toolbar onto that sheet and assign it this
macro:

Option Explicit
Sub testme01()
Dim TmpWks As Worksheet
Dim NewWks As Worksheet
Dim myStr As String

myStr = Format(Date, "mmmm-yyyy")

Set NewWks = Nothing
On Error Resume Next
Set NewWks = Worksheets(myStr)
On Error GoTo 0

If NewWks Is Nothing Then
'doesn't exist, keep going
Else
MsgBox "That sheet already exists!"
Exit Sub
End If

Set TmpWks = Worksheets("Template")
With TmpWks
.Visible = xlSheetVisible
.Copy _
after:=Worksheets(Worksheets.Count)
Set NewWks = ActiveSheet
.Visible = xlSheetHidden
End With

With NewWks
.Name = myStr
With .Range("a1")
.Value = Date
.NumberFormat = "mmmm-yyyy"
End With
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ann~ wrote:

I keep track of monthly finances with a simple spreadsheet. I keep one sheet
unedited to copy/paste each month into a new sheet (current month-year), then
fill in all the relevant data (dollar amounts/rates/misc fees/etc).

What I would like to have is a button or something that will create a copy
of this original/template, name it (with the current month-year), open it so
it's the currently viewed screen, and have the name of the sheet (current
month-year) in cell A1.

I haven't a clue where to start so any help will be greatly appreciated.

Many thanks,
Ann~

--

Dave Peterson


--

Dave Peterson

Yes! This is just what I was looking for.

Please stick with me for a few more tweaks€¦

1.) On the newly created €œcurrent month-year" sheet, cell A1 is set to show
the mmmm-yyyy. How can I change that so it displays the mmmm-yyyy followed
by the word Portfolio on cell B2 (aka January-2007 Portfolio)?

2.) On the "index" sheet, I'd like to have list of each months total assets:
with the most recent months data as the first entry under the header.

The dollar amount will always be in cell G2 of each edited monthly worksheet.

The B column will list the sheets name (mmmm-yyyy)
The C column wil list the sheets G2 data


This is what I'm thinking about€¦
B15 = Investment & Personal Assets
B16 = January 2007 C16 = amount from current months G2
B17 = December 2006 C16 = December s G2
B18 = November 2006 C17 = November s G2

This is going to be awesome!

Thanks,
Ann~


"Dave Peterson" wrote:

I don't understand the first question.

But the second can be accomplished by adding a bit more to the code.

Option Explicit
Sub testme01()
Dim TmpWks As Worksheet
Dim NewWks As Worksheet
Dim myStr As String
Dim ActSheet As Worksheet
Dim DestCell As Range

Set ActSheet = ActiveSheet

myStr = Format(Date, "mmmm-yyyy")

Set NewWks = Nothing
On Error Resume Next
Set NewWks = Worksheets(myStr)
On Error GoTo 0

If NewWks Is Nothing Then
'doesn't exist, keep going
Else
MsgBox "That sheet already exists!"
Exit Sub
End If

Set TmpWks = Worksheets("Template")
With TmpWks
.Visible = xlSheetVisible
.Copy _
after:=Worksheets(Worksheets.Count)
Set NewWks = ActiveSheet
.Visible = xlSheetHidden
End With

With NewWks
.Name = myStr
With .Range("a1")
.Value = Date
.NumberFormat = "mmmm-yyyy"
End With
End With

'added (along with the declaration statements at the top)
With ActSheet 'the sheet with the button, just to avoid names
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
DestCell.Formula = "='" & NewWks.Name & "'!c7"
End With

End Sub

I put the value of the newsheet's c7 in the next available cell in column A.


==========
Ahhh. I think I understand the first question!

You used a commandbutton from the Control toolbox toolbar. Throw that away
(along with its _click procedure) and replace it with a button from the Forms
toolbar.

And remember this code will go in a General module--not behind the worksheet.

Ann~ wrote:

Thanks for your help Dave ~ I've used Excel for years, but never before like
this!

I've named the original sheet "template," recorded the macro you gave me,
and am totally thrilled with the results! You've given me confidence that
I'll have this working right shortly. However, if you've got some spare
time, I could use help on some of the basics...

I've gotten the "index" sheet, for lack of a better term, looking fairly
nice. It has general info/instructions as well as the button you told me to
add.

Two things have me stumped...

1.) How do I incorporate the macro data that you gave me with the preset
data that showed up when the button was created? (Forgive if this is
stupid.) I've unsuccessfully tried it below the existing text, in between
the existing text, and in place of the existing text.

2.) How would a formula be written that would show the results of cell C7 of
the most recently created sheet (aka "current month-year")? I'd like to
include this on the "index" sheet.

Ann
~*~

"Dave Peterson" wrote:

I would hide that template sheet to keep it safely out of the way.

Then I'd add instruction worksheet that describes what needs to be done. And
I'd drop a button from the Forms toolbar onto that sheet and assign it this
macro:

Option Explicit
Sub testme01()
Dim TmpWks As Worksheet
Dim NewWks As Worksheet
Dim myStr As String

myStr = Format(Date, "mmmm-yyyy")

Set NewWks = Nothing
On Error Resume Next
Set NewWks = Worksheets(myStr)
On Error GoTo 0

If NewWks Is Nothing Then
'doesn't exist, keep going
Else
MsgBox "That sheet already exists!"
Exit Sub
End If

Set TmpWks = Worksheets("Template")
With TmpWks
.Visible = xlSheetVisible
.Copy _
after:=Worksheets(Worksheets.Count)
Set NewWks = ActiveSheet
.Visible = xlSheetHidden
End With

With NewWks
.Name = myStr
With .Range("a1")
.Value = Date
.NumberFormat = "mmmm-yyyy"
End With
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ann~ wrote:

I keep track of monthly finances with a simple spreadsheet. I keep one sheet
unedited to copy/paste each month into a new sheet (current month-year), then
fill in all the relevant data (dollar amounts/rates/misc fees/etc).

What I would like to have is a button or something that will create a copy
of this original/template, name it (with the current month-year), open it so
it's the currently viewed screen, and have the name of the sheet (current
month-year) in cell A1.

I haven't a clue where to start so any help will be greatly appreciated.

Many thanks,
Ann~

--

Dave Peterson


--

Dave Peterson

#1.

I'd use a formula:
=text(a1,"mmmm-yyyy") & " Portfolio"
in the template worksheet.

But you could use code, too:

Option Explicit
Sub testme01()
Dim TmpWks As Worksheet
Dim NewWks As Worksheet
Dim myStr As String
Dim ActSheet As Worksheet
Dim DestCell As Range

Set ActSheet = ActiveSheet

myStr = Format(Date, "mmmm-yyyy")

Set NewWks = Nothing
On Error Resume Next
Set NewWks = Worksheets(myStr)
On Error GoTo 0

If NewWks Is Nothing Then
'doesn't exist, keep going
Else
MsgBox "That sheet already exists!"
Exit Sub
End If

Set TmpWks = Worksheets("Template")
With TmpWks
.Visible = xlSheetVisible
.Copy _
after:=Worksheets(Worksheets.Count)
Set NewWks = ActiveSheet
.Visible = xlSheetHidden
End With

With NewWks
.Name = myStr
With .Range("a1")
.Value = Date
.NumberFormat = "mmmm-yyyy"
End With
With .Range("b2")
.Value = myStr & " Portfolio"
End With

'added (along with the declaration statements at the top)
With ActSheet 'the sheet with the button, just to avoid names
.Rows(16).Insert
With .Range("B16")
.NumberFormat = "@"
.Value = NewWks.Name
.Offset(0, 1).Formula = "=INDIRECT(""'""&B16 &""'!g2"")"
End With
End With

End With

End Sub

I inserted a whole new row to add the G2 stuff.

Ann~ wrote:

Yes! This is just what I was looking for.

Please stick with me for a few more tweaks€¦

1.) On the newly created €œcurrent month-year" sheet, cell A1 is set to show
the mmmm-yyyy. How can I change that so it displays the mmmm-yyyy followed
by the word Portfolio on cell B2 (aka January-2007 Portfolio)?

2.) On the "index" sheet, I'd like to have list of each months total assets:
with the most recent months data as the first entry under the header.

The dollar amount will always be in cell G2 of each edited monthly worksheet.

The B column will list the sheets name (mmmm-yyyy)
The C column wil list the sheets G2 data

This is what I'm thinking about€¦
B15 = Investment & Personal Assets
B16 = January 2007 C16 = amount from current months G2
B17 = December 2006 C16 = December s G2
B18 = November 2006 C17 = November s G2

This is going to be awesome!

Thanks,
Ann~

"Dave Peterson" wrote:

I don't understand the first question.

But the second can be accomplished by adding a bit more to the code.

Option Explicit
Sub testme01()
Dim TmpWks As Worksheet
Dim NewWks As Worksheet
Dim myStr As String
Dim ActSheet As Worksheet
Dim DestCell As Range

Set ActSheet = ActiveSheet

myStr = Format(Date, "mmmm-yyyy")

Set NewWks = Nothing
On Error Resume Next
Set NewWks = Worksheets(myStr)
On Error GoTo 0

If NewWks Is Nothing Then
'doesn't exist, keep going
Else
MsgBox "That sheet already exists!"
Exit Sub
End If

Set TmpWks = Worksheets("Template")
With TmpWks
.Visible = xlSheetVisible
.Copy _
after:=Worksheets(Worksheets.Count)
Set NewWks = ActiveSheet
.Visible = xlSheetHidden
End With

With NewWks
.Name = myStr
With .Range("a1")
.Value = Date
.NumberFormat = "mmmm-yyyy"
End With
End With

'added (along with the declaration statements at the top)
With ActSheet 'the sheet with the button, just to avoid names
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
DestCell.Formula = "='" & NewWks.Name & "'!c7"
End With

End Sub

I put the value of the newsheet's c7 in the next available cell in column A.


==========
Ahhh. I think I understand the first question!

You used a commandbutton from the Control toolbox toolbar. Throw that away
(along with its _click procedure) and replace it with a button from the Forms
toolbar.

And remember this code will go in a General module--not behind the worksheet.

Ann~ wrote:

Thanks for your help Dave ~ I've used Excel for years, but never before like
this!

I've named the original sheet "template," recorded the macro you gave me,
and am totally thrilled with the results! You've given me confidence that
I'll have this working right shortly. However, if you've got some spare
time, I could use help on some of the basics...

I've gotten the "index" sheet, for lack of a better term, looking fairly
nice. It has general info/instructions as well as the button you told me to
add.

Two things have me stumped...

1.) How do I incorporate the macro data that you gave me with the preset
data that showed up when the button was created? (Forgive if this is
stupid.) I've unsuccessfully tried it below the existing text, in between
the existing text, and in place of the existing text.

2.) How would a formula be written that would show the results of cell C7 of
the most recently created sheet (aka "current month-year")? I'd like to
include this on the "index" sheet.

Ann
~*~

"Dave Peterson" wrote:

I would hide that template sheet to keep it safely out of the way.

Then I'd add instruction worksheet that describes what needs to be done. And
I'd drop a button from the Forms toolbar onto that sheet and assign it this
macro:

Option Explicit
Sub testme01()
Dim TmpWks As Worksheet
Dim NewWks As Worksheet
Dim myStr As String

myStr = Format(Date, "mmmm-yyyy")

Set NewWks = Nothing
On Error Resume Next
Set NewWks = Worksheets(myStr)
On Error GoTo 0

If NewWks Is Nothing Then
'doesn't exist, keep going
Else
MsgBox "That sheet already exists!"
Exit Sub
End If

Set TmpWks = Worksheets("Template")
With TmpWks
.Visible = xlSheetVisible
.Copy _
after:=Worksheets(Worksheets.Count)
Set NewWks = ActiveSheet
.Visible = xlSheetHidden
End With

With NewWks
.Name = myStr
With .Range("a1")
.Value = Date
.NumberFormat = "mmmm-yyyy"
End With
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ann~ wrote:

I keep track of monthly finances with a simple spreadsheet. I keep one sheet
unedited to copy/paste each month into a new sheet (current month-year), then
fill in all the relevant data (dollar amounts/rates/misc fees/etc).

What I would like to have is a button or something that will create a copy
of this original/template, name it (with the current month-year), open it so
it's the currently viewed screen, and have the name of the sheet (current
month-year) in cell A1.

I haven't a clue where to start so any help will be greatly appreciated.

Many thanks,
Ann~

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

ps. The code included the changes for the second question, too.

Dave Peterson wrote:

#1.

I'd use a formula:
=text(a1,"mmmm-yyyy") & " Portfolio"
in the template worksheet.

But you could use code, too:

Option Explicit
Sub testme01()
Dim TmpWks As Worksheet
Dim NewWks As Worksheet
Dim myStr As String
Dim ActSheet As Worksheet
Dim DestCell As Range

Set ActSheet = ActiveSheet

myStr = Format(Date, "mmmm-yyyy")

Set NewWks = Nothing
On Error Resume Next
Set NewWks = Worksheets(myStr)
On Error GoTo 0

If NewWks Is Nothing Then
'doesn't exist, keep going
Else
MsgBox "That sheet already exists!"
Exit Sub
End If

Set TmpWks = Worksheets("Template")
With TmpWks
.Visible = xlSheetVisible
.Copy _
after:=Worksheets(Worksheets.Count)
Set NewWks = ActiveSheet
.Visible = xlSheetHidden
End With

With NewWks
.Name = myStr
With .Range("a1")
.Value = Date
.NumberFormat = "mmmm-yyyy"
End With
With .Range("b2")
.Value = myStr & " Portfolio"
End With

'added (along with the declaration statements at the top)
With ActSheet 'the sheet with the button, just to avoid names
.Rows(16).Insert
With .Range("B16")
.NumberFormat = "@"
.Value = NewWks.Name
.Offset(0, 1).Formula = "=INDIRECT(""'""&B16 &""'!g2"")"
End With
End With

End With

End Sub

I inserted a whole new row to add the G2 stuff.

Ann~ wrote:

Yes! This is just what I was looking for.

Please stick with me for a few more tweaks€¦

1.) On the newly created €œcurrent month-year" sheet, cell A1 is set to show
the mmmm-yyyy. How can I change that so it displays the mmmm-yyyy followed
by the word Portfolio on cell B2 (aka January-2007 Portfolio)?

2.) On the "index" sheet, I'd like to have list of each months total assets:
with the most recent months data as the first entry under the header.

The dollar amount will always be in cell G2 of each edited monthly worksheet.

The B column will list the sheets name (mmmm-yyyy)
The C column wil list the sheets G2 data

This is what I'm thinking about€¦
B15 = Investment & Personal Assets
B16 = January 2007 C16 = amount from current months G2
B17 = December 2006 C16 = December s G2
B18 = November 2006 C17 = November s G2

This is going to be awesome!

Thanks,
Ann~

"Dave Peterson" wrote:

I don't understand the first question.

But the second can be accomplished by adding a bit more to the code.

Option Explicit
Sub testme01()
Dim TmpWks As Worksheet
Dim NewWks As Worksheet
Dim myStr As String
Dim ActSheet As Worksheet
Dim DestCell As Range

Set ActSheet = ActiveSheet

myStr = Format(Date, "mmmm-yyyy")

Set NewWks = Nothing
On Error Resume Next
Set NewWks = Worksheets(myStr)
On Error GoTo 0

If NewWks Is Nothing Then
'doesn't exist, keep going
Else
MsgBox "That sheet already exists!"
Exit Sub
End If

Set TmpWks = Worksheets("Template")
With TmpWks
.Visible = xlSheetVisible
.Copy _
after:=Worksheets(Worksheets.Count)
Set NewWks = ActiveSheet
.Visible = xlSheetHidden
End With

With NewWks
.Name = myStr
With .Range("a1")
.Value = Date
.NumberFormat = "mmmm-yyyy"
End With
End With

'added (along with the declaration statements at the top)
With ActSheet 'the sheet with the button, just to avoid names
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
DestCell.Formula = "='" & NewWks.Name & "'!c7"
End With

End Sub

I put the value of the newsheet's c7 in the next available cell in column A.


==========
Ahhh. I think I understand the first question!

You used a commandbutton from the Control toolbox toolbar. Throw that away
(along with its _click procedure) and replace it with a button from the Forms
toolbar.

And remember this code will go in a General module--not behind the worksheet.

Ann~ wrote:

Thanks for your help Dave ~ I've used Excel for years, but never before like
this!

I've named the original sheet "template," recorded the macro you gave me,
and am totally thrilled with the results! You've given me confidence that
I'll have this working right shortly. However, if you've got some spare
time, I could use help on some of the basics...

I've gotten the "index" sheet, for lack of a better term, looking fairly
nice. It has general info/instructions as well as the button you told me to
add.

Two things have me stumped...

1.) How do I incorporate the macro data that you gave me with the preset
data that showed up when the button was created? (Forgive if this is
stupid.) I've unsuccessfully tried it below the existing text, in between
the existing text, and in place of the existing text.

2.) How would a formula be written that would show the results of cell C7 of
the most recently created sheet (aka "current month-year")? I'd like to
include this on the "index" sheet.

Ann
~*~

"Dave Peterson" wrote:

I would hide that template sheet to keep it safely out of the way.

Then I'd add instruction worksheet that describes what needs to be done. And
I'd drop a button from the Forms toolbar onto that sheet and assign it this
macro:

Option Explicit
Sub testme01()
Dim TmpWks As Worksheet
Dim NewWks As Worksheet
Dim myStr As String

myStr = Format(Date, "mmmm-yyyy")

Set NewWks = Nothing
On Error Resume Next
Set NewWks = Worksheets(myStr)
On Error GoTo 0

If NewWks Is Nothing Then
'doesn't exist, keep going
Else
MsgBox "That sheet already exists!"
Exit Sub
End If

Set TmpWks = Worksheets("Template")
With TmpWks
.Visible = xlSheetVisible
.Copy _
after:=Worksheets(Worksheets.Count)
Set NewWks = ActiveSheet
.Visible = xlSheetHidden
End With

With NewWks
.Name = myStr
With .Range("a1")
.Value = Date
.NumberFormat = "mmmm-yyyy"
End With
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ann~ wrote:

I keep track of monthly finances with a simple spreadsheet. I keep one sheet
unedited to copy/paste each month into a new sheet (current month-year), then
fill in all the relevant data (dollar amounts/rates/misc fees/etc).

What I would like to have is a button or something that will create a copy
of this original/template, name it (with the current month-year), open it so
it's the currently viewed screen, and have the name of the sheet (current
month-year) in cell A1.

I haven't a clue where to start so any help will be greatly appreciated.

Many thanks,
Ann~

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson