Solved: Error in Workbook Code

[kisinana]

I have a problem with my workbook code

[VBA]
Private Sub Workbook_Open()
If ActiveWorkbook.Name = "Test1" Then
' Application.Dialogs(xlDialogSaveAs).Show
ActiveWorkbook.SaveAs "Test " & Format$(Date, "yyyy-mm-dd") & ".xls"
End If
End Sub

[/VBA]
It works fine the first day, as soon as I go to another date my macros look for the file with the first day I used.
What I am looking to do is have the macros always operate from the file that is the active workbook.

[mdmackillop]

I don't follow your problem. The macro can only run from the file created by the template.
Are you always saving to the same folder, or do you need the dialog box?

[kisinana]

Yesteday I created a file with yesterdays date (Test 2008-07-22) and I ran the macros OK.
Today I create a new file (test 2008-07-23) with todays date and it runs the macro fine but my last step in the macro is to save active workbook, it does not. What it does is resaves yestedays file again. So I look in the macro and now the macro name shows as being linked to yesterdays date

[vba]
'
Range("D1:F73").Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:=Range("E2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Range("F2").Select
ActiveWorkbook.Save
End Sub
[/vba]

[mdmackillop]

What I want is as soon as a template is chosen the first thing it does is come back and ask to be saved.

The code as supplied was to carry out this specific task ie, name a file created by a template ".xlt" file.
If you want to save the created workbook with another nane/date when it opens, that is a different issue. Are you creating a new file every day, or are you trying to save the one file with a new date suffix every day?

[kisinana]

It creates a new file each time used with the same name and a different date. It uses the same template just the information in some of the fields changes and has to be resorted. Then I resave the file it never has to go back to the previous date as the information in it is now irrelevant. But if I need to compare across a couple of weeks I have copies of the files with different dates to go by.

[kisinana]

Mdmackillop
When I look in the macro box that shows all my macros, it shows as all my macros and a copy of each with the original days date. So I am on a third day and it still reverts the activeworkbook save to the first copy of the original XLT file. I hope this makes sense.

The code as supplied was to carry out this specific task ie, name a file created by a template ".xlt" file.
If you want to save the created workbook with another nane/date when it opens, that is a different issue. Are you creating a new file every day, or are you trying to save the one file with a new date suffix every day?

Can this be part of the problem that Test.xlt first creates Test1.xls that I then ask to rename Test yyyy-mm-dd.xls?

[mdmackillop]

When you create a new workbook from a template called Test, the workbook will be called Test1 (not Test1.xls), so the If function only works with a newly created book.
This code will save the new workbook with the date suffix.
[VBA]
Private Sub Workbook_Open()
If ActiveWorkbook.Name = "Test1" Then
ActiveWorkbook.SaveAs "C:\AAA\" & "Test " & Format$(Date, "yyyy-mm-dd") & ".xls"
End If
End Sub

[/VBA]
When you open a dated file, there is no save triggered.
Can you post one of the saved workbooks?

[kisinana]

Not sure what you mean by post a saved workbook? As a print screen or ?

[mdmackillop]

You can post it using Managed Attachments in the Go Advanced reply section.

[kisinana]

Attached. I deleted all names. Teeoffs are entered first, This is a list of people who may show up generally about 20. Groupings are by the order they show. Scores are entered at the end and then randomly paired up to give a pair scoring. I need to remove some information and I will repost tomorrow.

[kisinana]

Mdmackillop
I ran the program again and now it opens all dates of the file. Because it has a random number generator in use it asks to save all copies when I go to exit.
Any questions let me know.

[kisinana]

I have a little more information to go on. I had created 2 custom buttons and assigned each a macro. If I use the CTRL-T or CTRL-D method to run the macros they do not search for another file, but if I use the custom buttons that is when the problem seems to arise with it openning a past copy of the file and saving it as well.
If I delete the files and start the .xlt is looks for and cannot find the last file, so I open the .xlt and go into macros and clear the macro name looking for the filename with a date. Then the program runs once correctly, change the computer date and run again and it opens the previous file as well.
So it looks like I need a way to have the custom buttons only select the current file.

[mdmackillop]

Now I see yor problem. When you use Forms command buttons, you assign the macro to the button. The button looks for the macro in its original location.
Use ActiveX controls instead, from the Controls toolbox. These have a code procedure stored in the Worksheet module. You can either add your code into the click event, or, as in the attached sample, call the code which is in the standard modules.

[kisinana]

Thanks for all the help.