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