using two cell references to make up the save as file name
 

How do I use two cells to make up the file save name.

eg. A1 and D1 (what ever)

Thanks

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)


mydrive = "H:"
mydir = "Temp"
myname = Sheets("sheet1").Range("a1")
ms = mydrive & "\" & mydir & "\" & myname & Format$(Date, " dd-mm-
yyyy") & "xls"
ActiveWorkbook.SaveCopyAs Filename:=ms







' Place the current files path and filename in the titlebar:
Windows(1).Caption = ActiveWorkbook.FullName



' Place your own application name in the titlebar:
Application.Caption = "SPICE SHEET FOLDER"





Cancel = True
ActiveWorkbook.Saved = True
msg = MsgBox("The workbook has been saved as " & ms, vbInformation +
vbOKOnly, "Save As")
Application.DisplayFullScreen = False
End Sub

using two cell references to make up the save as file name
 

On Oct 3, 3:35*pm, Johnnyboy5 wrote:
How do I use two cells to make up the file save name.

eg. * A1 * and *D1 * (what ever)

Thanks

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

mydrive = "H:"
mydir = "Temp"
myname = Sheets("sheet1").Range("a1")
ms = mydrive & "\" & mydir & "\" & myname & Format$(Date, " dd-mm-
yyyy") & "xls"
ActiveWorkbook.SaveCopyAs Filename:=ms

' Place the current files path and filename in the titlebar:
* * * Windows(1).Caption = ActiveWorkbook.FullName

* * * ' Place your own application name in the titlebar:
* * * Application.Caption = "SPICE SHEET FOLDER"

Cancel = True
ActiveWorkbook.Saved = True
msg = MsgBox("The workbook has been saved as " & ms, vbInformation +
vbOKOnly, "Save As")
Application.DisplayFullScreen = False
End Sub

hi
use the ampersand concatenater.
you can concatenate as many cells as you want.

Sub myname()
Dim mn As String
mn = Range("A2").Value & Range("B2").Value
MsgBox mn
End Sub

regards
FSt1

using two cell references to make up the save as file name
 

On 3 Oct, 21:49, FSt1 wrote:
On Oct 3, 3:35*pm, Johnnyboy5 wrote:





How do I use two cells to make up the file save name.

eg. * A1 * and *D1 * (what ever)

Thanks

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

mydrive = "H:"
mydir = "Temp"
myname = Sheets("sheet1").Range("a1")
ms = mydrive & "\" & mydir & "\" & myname & Format$(Date, " dd-mm-
yyyy") & "xls"
ActiveWorkbook.SaveCopyAs Filename:=ms

' Place the current files path and filename in the titlebar:
* * * Windows(1).Caption = ActiveWorkbook.FullName

* * * ' Place your own application name in the titlebar:
* * * Application.Caption = "SPICE SHEET FOLDER"

Cancel = True
ActiveWorkbook.Saved = True
msg = MsgBox("The workbook has been saved as " & ms, vbInformation +
vbOKOnly, "Save As")
Application.DisplayFullScreen = False
End Sub

hi
use the ampersand concatenater.
you can concatenate as many cells as you want.

Sub myname()
Dim mn As String
mn = Range("A2").Value & Range("B2").Value
MsgBox mn
End Sub

regards
FSt1

thanks

added your answer to the macro, the only thing I would like to add is
that there is a "space" between A2 and B" in the file name - eg.
NAME "" 1234556

then I am done !

thanks john




Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

mydrive = "H:"
mydir = "Temp"
myname = Sheets("sheet1").Range("a1").Value & Range("a3").Value
ms = mydrive & "\" & mydir & "\" & myname & ".xls"
ActiveWorkbook.SaveCopyAs Filename:=ms







' Place the current files path and filename in the titlebar:
Windows(1).Caption = ActiveWorkbook.FullName



' Place your own application name in the titlebar:
Application.Caption = "SPICE SHEET FOLDER"





Cancel = True
ActiveWorkbook.Saved = True
msg = MsgBox("The workbook has been saved as " & ms, vbInformation +
vbOKOnly, "Save As")
Application.DisplayFullScreen = False
End Sub

using two cell references to make up the save as file name
 

On 3 Oct, 21:49, FSt1 wrote:
On Oct 3, 3:35*pm, Johnnyboy5 wrote:





How do I use two cells to make up the file save name.

eg. * A1 * and *D1 * (what ever)

Thanks

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

mydrive = "H:"
mydir = "Temp"
myname = Sheets("sheet1").Range("a1")
ms = mydrive & "\" & mydir & "\" & myname & Format$(Date, " dd-mm-
yyyy") & "xls"
ActiveWorkbook.SaveCopyAs Filename:=ms

' Place the current files path and filename in the titlebar:
* * * Windows(1).Caption = ActiveWorkbook.FullName

* * * ' Place your own application name in the titlebar:
* * * Application.Caption = "SPICE SHEET FOLDER"

Cancel = True
ActiveWorkbook.Saved = True
msg = MsgBox("The workbook has been saved as " & ms, vbInformation +
vbOKOnly, "Save As")
Application.DisplayFullScreen = False
End Sub

hi
use the ampersand concatenater.
you can concatenate as many cells as you want.

Sub myname()
Dim mn As String
mn = Range("A2").Value & Range("B2").Value
MsgBox mn
End Sub

regards
FSt1

Sorted - thanks VM.

Johnnyboy