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