I have recorded an Excel Macro in the past but never coded one until recently. I was prompted by a question in an online forum posting to learn, implement and respond to the query with a working code sample.The requirement was to automatically copy the previous day's worksheet to a new worksheet and name the sheet with the date of the day (in the format MM-DD-YY) on which it is re-opened. This basically helps in a scenario where you have to maintain a daily log.
Unlike in Excel 2003, where the option to create a Macro was available from the Menu (Tools > Macro), in Excel 2007 the option to get started with coding a Macro is present in the new Developer tab.
To get the Developer tab, click the Microsoft Office Button, and then click Excel Options button present at the bottom. In the dialog box that opens up, select the Popular category, then under the Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.
To set the security level temporarily to enable all macros, click Macro Security on the Developer tab in the Code group. In the dialog box that opens, under Macro Settings, click Enable all macros (not recommended, potentially dangerous code can run), and then click OK.
To code a new Macro, you can click on Visual Basic or Macros option in the Developer tab.
I choose Macros. In the dialog box that opens up, name the macro specifically as Auto_Open in the textbox for Macro Name.
As it is named Auto_Open it lets Excel know that this macro has to run automatically when you open the Excel file.
When you click on the Create button in the same dialog box, the VBA editor opens up with this template -
Sub Auto_Open()
End Sub
I adapted a snippet that I found online and below is how my code finally looked. It has to paste within the above 2 lines in the VBA editor.
Dim wks As Worksheet
'Create new worksheet in the active workbook, put it after last sheet
Set wks = ActiveWorkbook.Worksheets.Add
(After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count))
'Try naming the worksheet as todays date (sorry, no "/" allowed in sheet names)
On Error Resume Next
wks.Name = Format(Date, "MM-DD-YY")
On Error GoTo 0
'Show a message if rename failed
If wks.Name <> Format(Date, "MM-DD-YY") Then
MsgBox "Sorry. Couldn't rename sheet to today's date."
End If
'Copy previous sheet to newly created sheet
ActiveWorkbook.Worksheets
(ActiveWorkbook.Sheets.Count-1).Cells.Copy Destination:=wks.Cells
'Select cell A1 of newly created sheet
wks.Select
wks.Range("A1").Select
'Release object variables
Set wks = Nothing
Save it. A worksheet with a template has to saved before hand so that the macro can use that as a base copy to create new sheets with current date and contents of the previous sheet.
Related link:
HOW TO create a Ribbon-less Excel 2003 UI in Excel 2007