About Us

Monk[_2_]

Thanks Norman. Perfect.

"Norman Jones" wrote:

Hi Monk,

Perhaps try:

'=========
Option Explicit

Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim aStr As String
Dim sPath As String
Dim FName As String
Const sStr As String = "F:\Home\"
Const sStr2 = "\Trading\2.Draft Trades.xls"

Set WB = ThisWorkbook
Set SH = WB.Sheets("Sheet2") '<<==== CHANGE
Set Rng = SH.Range("A1") '<<==== CHANGE

If Not IsEmpty(Rng.Value) Then
aStr = Rng.Value
FName = sStr & aStr & sStr2
ThisWorkbook.SaveAs Filename:=FName, _
FileFormat:=xlWorkbookNormal
Else
'Your code to handle misssing data, e.g.:
MsgBox Prompt:="Your message", _
Buttons:=vbCritical, _
Title:="Problem"

End If
End Sub
'<<=========

---
Regards.
Norman

"Monk" wrote in message
...
Thanks Norman

I was hoping there may be something less complex. The following code is
part
of an existing module and I was hoping it could pick up the value of cell
A1
(i.e say value = Paul) and enter this in the directory so it would save as
F:\Home\Paul\Trading\2.Draft Trades.xls. If A1 had a value of Mike it
would
save as F:\Home\Mike\Trading\2.Draft Trades.xls. Can the below be
modified
simply?

fileSaveName = Application.GetSaveAsFilename("F:\Home\Cell
A1\Trading\2.Draft Trades.xls", _
"Excel Files (*.xls), *.xls")

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Cell Location Reference	Roy A.[_2_]	Excel Discussion (Misc queries)	2	March 26th 09 12:01 AM
Creating a linked file based on variable location reference	Monk[_2_]	Excel Discussion (Misc queries)	2	May 18th 08 10:47 AM
Cell Reference Location	CWillis	Excel Discussion (Misc queries)	2	June 7th 06 07:54 PM
Reference cell location	Dan	Excel Worksheet Functions	7	March 1st 06 04:18 AM
Cell Reference to a location	Barney[_2_]	Excel Programming	3	December 14th 04 02:07 AM

Menu

About Us