Solved: Auto open macro help

[solderhead]

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

[lucas]

Welcome to the board.

This doesn't look like it would be hard to do but I have a couple of questions.

First, you want it to copy the new sheet each time it is opened if it doesn't exist. What if you skip a day, does that matter?

In other words, do you actually want a sheet for each workday of the month or just a new sheet based on the template each time it's opened if it doesn't exist.

.........................

Next, what if the template sheet resides in the excel workbook itself instead of calling it from an external .xlt file, it can be hidden and you avoid the possibility of the template path being changed or lost to your file.

....................
third, auto open is not the best way to call a procedure when the file opens. It is better to look in the thisworkbook module and in the left dropdown select workbook and in the right dropdown select the Workbook open procedure to house your code.
................................

If you can post your template file it would help. Remove any personal or company info and hit the go advanced button, then scroll down and look for the button that says manage attachments.

Also, when posting code, select the code and hit the green VBA button to format it for the forum as I have done to your first post.

[solderhead]

Thanks for the reply Lucas.

At the moment I'm at work so I don't have the spreadsheet with me and I don't really have the time to answer your questions fully.

I'll get back to you later with some answers.

Thanks again.

[solderhead]

Hi Lucas

Sorry for the delay in getting back. For some reason I was unable to access the site from my home computer. Worked okay yesterday. Was the site down for maintenance?

Anyway, on to your questions:


First, you want it to copy the new sheet each time it is opened if it doesn't exist. What if you skip a day, does that matter?
Skipping a day is no problem. I’d rather have it that way as I work a four day on, four day off routine, so I don’t need the four off adding unused sheets. Just a new sheet based on the template (with the correct date on the tab), if it doesn’t already exist, each time it is opened. If I open the workbook on a day off I can easily delete the additional sheet as and when it happens.




Next, what if the template sheet resides in the excel workbook itself instead of calling it from an external .xlt file, it can be hidden and you avoid the possibility of the template path being changed or lost to your file.
Good idea! I didn’t realize that was an option but it would be much better to do it that way.




third, auto open is not the best way to call a procedure when the file opens. It is better to look in the thisworkbook module and in the left dropdown select workbook and in the right dropdown select the Workbook open procedure to house your code.
I’m happy to go with whatever you think is best. I have tinkered with spreadsheets before but I’m definitely no expert and I have zero experience with macros.




If you can post your template file it would help. Remove any personal or company info and hit the go advanced button, then scroll down and look for the button that says manage attachments.
Will do. Hopefully it should be attached to this message. I had to send it as an xls file, rather than xlt, as when I tried to upload it I received a message that it was an invalid file. (xlt files are not listed amongst the acceptable file types).




Also, when posting code, select the code and hit the green VBA button to format it for the forum as I have done to your first post.
Thanks for the tip.

[lucas]

Hide the template sheet and put this in the thisworkbook module:


Option Explicit
Private Sub Workbook_Open()
Dim TotalSheets As Long
TotalSheets = Worksheets.Count - 1
Worksheets("Template").Activate
ActiveSheet.Copy after:=Worksheets(TotalSheets)
Worksheets("Template (2)").Activate
On Error GoTo Exits
ActiveSheet.Name = Format(Date, "dd-mmm-yy")
ActiveSheet.Visible = True
Exits:
End Sub


See attached example

[solderhead]

Perfect!

I can't tell you how grateful I am.

Thanks very much Lucas. I really appreciate it.

[lucas]

You won't be so happy with me when you find out what I have overlooked on this.

A friend who thankfully looks over my shoulder has pointed out that by doing things the way I did on this project, it will cause many copies of the template file to be created and remain hidden. Thankfully he also provided a simple and elegant solution.

Download the file at post #5 and go to format-unhide to see what has been going on without us catching it.

Attached is the same file with the extra template files deleted and the code updated.

This code will check to see if you already have a sheet with that name before proceeding. My original code went ahead and copied the sheet before it decided to exit the sub or name the sheet. If it exited, there was still a copy of the template file hidden from view......arg.

Private Sub Workbook_Open()
Dim TotalSheets As Long
Dim ws As Worksheet
On Error Resume Next
Set ws = Sheets(Format(Date, "dd-mmm-yy"))
If ws Is Nothing Then
TotalSheets = Worksheets.Count - 1
Worksheets("Template").Activate
ActiveSheet.Copy after:=Worksheets(TotalSheets)
Worksheets("Template (2)").Activate
On Error GoTo Exits
ActiveSheet.Name = Format(Date, "dd-mmm-yy")
ActiveSheet.Visible = True
End If
Exits:
End Sub

If you want to use the workbook from previous posts and want to unhide all the template files at once you can use this and delete all the copies of the template and then hide just the one sheet named template.

'Unhide all sheets
Sub ViewAll()
Dim sh As Worksheet
For Each sh In Sheets
sh.Visible = True
Next
End Sub

[solderhead]

Thanks for the excellent after sales service.