Try

Application.Dialogs(xlDialogSaveAs).Show ThisWorkbook.Path _
& "\Submittal Transmittals\" & Fname & ".xls"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Casey"
wrote in message
...

Hi,
Jim Thomlinson gave me some great code last week for saving a
copied
worksheet to a subfolder with the same path as the original
workbook.
My problem is that I can't seem to make the file name a varible
based
on a worksheet range value. I just get the hard coded name. I
could use
some help on the correct syntax.

Here is my current code.

Private Sub cmdCopyTransmittal_Click()
Dim c As Range
Dim d As Range
Dim Fname As String

Sheets("TRANS(0)").Copy
ActiveSheet.Unprotect ("geekk")
Set d = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
For Each c In d
With c
Value = .Value
End With
Next c
ActiveSheet.Shapes("cmdCopyTransmittal").Delete
ActiveSheet.Shapes("cmdImportSubmittals").Delete
ActiveSheet.Shapes("cmdAddRow").Delete
ActiveSheet.Shapes("cmdDeleteRow").Delete
Fname = ActiveSheet.Range("A9").Value
ActiveSheet.Protect ("geekk"), DrawingObjects:=True,
Contents:=True, _
Scenarios:=True
On Error Resume Next
MkDir ThisWorkbook.Path & "\Submittal Transmittals"
On Error GoTo 0
Application.Dialogs(xlDialogSaveAs).Show ThisWorkbook.Path _
& "\Submittal Transmittals\Fname.xls"

End Sub

The routine correctly creates the subfolder but names the file
"Fname"
instead of my varible.


--
Casey


------------------------------------------------------------------------
Casey's Profile:
http://www.excelforum.com/member.php...fo&userid=4545
View this thread:
http://www.excelforum.com/showthread...hreadid=519387