Create, name, open, and enter data in new sheet - from template

A

Ann~

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

Dave Peterson

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
 
D

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

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~ said:
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~
Click to expand...
 
Top