MACRO Saveas Keeping Current file open ?

[antfield]

I have the following Macro that I run assigned to a Button.

Sub AskAndDo()
If MsgBox("Have you Finished collecting data ?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
Else
If MsgBox("Have you Printed the Reports ?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
Else
ActiveWorkbook.SaveAs Filename:="\\gamingnt\company\FLOAT MASTER\Float_Sheet_" & Format(Date, "_YY_MM_DD")
End If
End If
Application.Quit
End Sub


What I need is to save as the file that this statement creates
{ActiveWorkbook.SaveAs Filename:="ActiveWorkbook.SaveAs Filename:="\\gamingnt\company\FLOAT MASTER\Float_Sheet_" & Format(Date, "_YY_MM_DD" }
BUT keep the current file open.
ie. I open file called Master_Float.xls
I click the Macro saving as Float_Sheet_yy_dd_mm, but I simply wish to just save it, but keep Master_Float.xls open ?

Any Idea's ?

 

[Mike Blackman]

Hi,

When you use save as and save a file withanother name, then the original file always disappears, does it not? Maybe before doing the SaveAs you could just save master_Float, then the Save as will remove it, then re-open Master Float and then close the Saved file.

Does that make sense?

 

[Yard]

I've got it in my head that there's a SaveCopyAs function or something similar, but can't seem to recall how/where to use it.

Will have a look around.

I may be dreaming it.

 

[Mike Blackman]

Quite right Yard,

ActiveWorkbook.SaveCopyAs C..........

Give this a try.

 

[antfield]

Thanks Guys...

I am a newbee to Scripts...

This was just doing my head in....

Regards..

 

[Yard]

Holy cow, what a dream!

 

[Mike Blackman]

In the nicest possible way Yard, Your a strange one

 

[antfield]

MACRO SaveCopyas

I am having an issue using the SavecopyAs command, when saved it does not have a file extention, like .xls.

I would like it to be saved as default .xls,
I would also like it so save with Read only attributes.

Example of current code...

ActiveWorkbook.SaveCopyAs Filename:="\\gamingnt\company\FLOAT MASTER\Float_Sheet_" & Format(Date, "_YY_MM_DD")

Can you also have the file when saved, if the desination file exists, add a number after the original file name, so it might look like Float_Sheet_08_11_14-1-

Thanks

 

[Mike Blackman]

Hi,

Then try;

ActiveWorkbook.SaveCopyAs Filename:="\\gamingnt\company\FLOAT MASTER\Float_Sheet_" & Format(Date, "_YY_MM_DD")[COLOR="Red"][B] & ".xls"[/B][/COLOR]

 

[antfield]

Thanks Works a treat...