Run Macro & Rename Sheet upon creating copy from blank.

[Guest]

Hello,

I am still in the early stages of VBA coding. Doing mostly comparisons thru
forum examples and support from a few people locally.

Big picture overview: I have a weekly report for which I have created a
blank template.

My intent is to create a copy of the blank worksheet and have two things
happen at that time; 1. run macro OpenCalendar() & 2. rename the worksheet
just created to "WE 101505". (WE for week ending and the date will come from
cell value in $O$2).

 

[Jzz]

Hi Kervin,

Why not put a button in you're blank form which calls something like:

Sub weekly()
Dim wbk1 As Workbook
Dim wbk2 As Workbook
Dim savename
Dim mydate As String

Set wbk1 = ActiveWorkbook
Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select 'this takes the
range until
' the last cell used you may want to adapt it to your own range
Selection.Copy
Range("A1").Select
Workbooks.Add (1)
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
Set wbk2 = ActiveWorkbook

Call calenderthing 'call your sub here

mydate = Range("O2").Value
savename = Application.GetSaveAsFilename( _
InitialFileName:="WE" + mydate) 'you can leave this one out if you
like
'then put something like : savename = "WE" + mydate + "."
If savename = False Then
wbk2.Saved = True
wbk2.Close
GoTo endsub
Else
savename = savename + "xls"
End If
wbk2.SaveAs Filename:=savename

endsub:
End Sub
Sub calenderthing()

End Sub

Then the copying is also done for you.

Grtz,

Jzz