solderhead
04-22-2010, 01:02 AM
I'm a new boy here so I'll say Hi to you all now :hi:.
I need some help getting my macro running the way I'd like it to run. I've literally only just started experimenting with macros a few hours ago and I've already lost a lot of sleep as a result.
I'd like it to start automatically when the workbook (and only this workbook) opens. Probably a very easy problem for most of you to fix but at this stage of my macro career it is not easy for me.
I have Excel 2003 and, if it makes a difference, I am running XP.
From the code below it's probably obvious to you guys but:
The template is called Shift timesheets.XLT
The workbook is called Shift timesheets
Basically it just opens a new sheet where I can keep a record of jobs carried out and time taken. The new sheet only opens once for each new calendar day and the sheet tab shows the date that it was opened.
Annoyingly, the first tab that opens with the initial opening of the workbook only shows 'sheet 1'. When I initiate the macro manually (after artificially advancing the current date) all subsequent worksheet tabs show the correct date.
I've cobbled it together from a few sites (Thanks to Justinlabenne on this forum - from a post entitle "Add a sheet with today's date". I can't post links until I've made 5 posts - forum rules). None of it is my own work.
I've experimented with "Sub Auto_Open()" "End Sub" with no luck.
I've carried out further searches on this site but am unable to get this thing working on opening of the workbook.
As already stated, the macro runs as required if I activate it manually once the workbook is open.
Here's what I have been able to rob from others so far:
Option Explicit
Sub AddSheets_Today()
' Add and name a sheet with today's date
Dim szToday As String
' Date Stamp:
szToday = Format(Date, "dd-mmm-yy")
On Error GoTo MakeSheet
' Check if sheet already exists:
' if it does, select activate it
Sheets(szToday).Activate
' No errors, we are done
Exit Sub
MakeSheet:
' If the sheet doesn't exist:
' Add it
Sheets.Add Type:= _
"C:\Documents and Settings\Andy Solyom\Application Data\Microsoft\Templates\Shift timesheets.XLT"
' Name it
ActiveSheet.Name = szToday
End Sub
I'd really appreciate it if you guys could indicate what I have to add to this code (and where I have to add it) to make it work. Better still if you could post the entire code package so that I can just copy and paste it. Less chance of a typo that way.
Assume that I am a VBA virgin and a total idiot.
Thanks in advance for any help.
Edit: VBA tags added to code
I need some help getting my macro running the way I'd like it to run. I've literally only just started experimenting with macros a few hours ago and I've already lost a lot of sleep as a result.
I'd like it to start automatically when the workbook (and only this workbook) opens. Probably a very easy problem for most of you to fix but at this stage of my macro career it is not easy for me.
I have Excel 2003 and, if it makes a difference, I am running XP.
From the code below it's probably obvious to you guys but:
The template is called Shift timesheets.XLT
The workbook is called Shift timesheets
Basically it just opens a new sheet where I can keep a record of jobs carried out and time taken. The new sheet only opens once for each new calendar day and the sheet tab shows the date that it was opened.
Annoyingly, the first tab that opens with the initial opening of the workbook only shows 'sheet 1'. When I initiate the macro manually (after artificially advancing the current date) all subsequent worksheet tabs show the correct date.
I've cobbled it together from a few sites (Thanks to Justinlabenne on this forum - from a post entitle "Add a sheet with today's date". I can't post links until I've made 5 posts - forum rules). None of it is my own work.
I've experimented with "Sub Auto_Open()" "End Sub" with no luck.
I've carried out further searches on this site but am unable to get this thing working on opening of the workbook.
As already stated, the macro runs as required if I activate it manually once the workbook is open.
Here's what I have been able to rob from others so far:
Option Explicit
Sub AddSheets_Today()
' Add and name a sheet with today's date
Dim szToday As String
' Date Stamp:
szToday = Format(Date, "dd-mmm-yy")
On Error GoTo MakeSheet
' Check if sheet already exists:
' if it does, select activate it
Sheets(szToday).Activate
' No errors, we are done
Exit Sub
MakeSheet:
' If the sheet doesn't exist:
' Add it
Sheets.Add Type:= _
"C:\Documents and Settings\Andy Solyom\Application Data\Microsoft\Templates\Shift timesheets.XLT"
' Name it
ActiveSheet.Name = szToday
End Sub
I'd really appreciate it if you guys could indicate what I have to add to this code (and where I have to add it) to make it work. Better still if you could post the entire code package so that I can just copy and paste it. Less chance of a typo that way.
Assume that I am a VBA virgin and a total idiot.
Thanks in advance for any help.
Edit: VBA tags added to code