Saving file using value in cell as the filename

[BCassedy]

I've researched this for some time without success (or because I'm not as
well versed in Excel, may have overlooked it...)
I'm trying to set up a trouble ticket spread sheet where there's minimal
technical input required by the user:
in Cell B3 = current date.
in Cell G1 = following formula (used to create a "trouble ticket number" ->
="301"&CONCATENATE(MONTH(B3),TEXT(DAY(B3),"00"),TEXT(YEAR(B3),"00"))
in Cell E14 I want to put a Command button which, when activated by the
user, will:
1. Save the file to a certain location on the hard drive (C:\OL Trbl
Tkts) -and-
use the value in Cell G1 (now reading 30107052007)
- which I'd like to cut down to 301070507 <ie. use only the
last 2 digits
of the year>
as the file name.

ie. the result upon clicking the command button on July 6, 2007 would result
in the current new worksheet being saved as C:\OL Trbl Tkts\301070607 <in an
ideal situation> -or- C:\OL Trbl Tkts\30107062007 as it now stands.

 

[Gord Dibben]

Sub Make_New_Book()
Dim mypath As String
mypath = "C:\OL Trbl Tkts"
Application.ScreenUpdating = False
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:=mypath & "\" & _
ActiveSheet.Range("G1").Value

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

 

[BCassedy]

Thank You!!!
I needed to modify the Sub name to reflect the use of a Command Button
(upon) Click:
Private Sub CommandButton1_Click()
, but once I did that, it works beautifully!

 

[Gord Dibben]

Thanks for the feedback.

Gord

Thank You!!!
I needed to modify the Sub name to reflect the use of a Command Button
(upon) Click:
Private Sub CommandButton1_Click()
, but once I did that, it works beautifully!