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"),TE XT(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.

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

On Thu, 5 Jul 2007 13:40:01 -0700, BCassedy
wrote:

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"),TE XT(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.

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" wrote:

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

On Thu, 5 Jul 2007 13:40:01 -0700, BCassedy
wrote:

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"),TE XT(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.

Thanks for the feedback.

Gord

On Fri, 6 Jul 2007 06:08:02 -0700, BCassedy
wrote:

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" wrote:

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

On Thu, 5 Jul 2007 13:40:01 -0700, BCassedy
wrote:

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"),TE XT(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.