File name varible problem

[Casey]

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.

 

[Chip Pearson]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[John]

Hi Casey,

Fname is a string variable, shouldn't be in the quotes. Try this:

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

Best regards

John

 

[Casey]

Chip,
Worked perfectly, thank you very much. I actually thought I had tried
that, but comparing your code to what I'd tried, I had neglected
quotation marks in the proper places.
Just a note of thanks as well for your excellent website, I highly
recommend it to anyone serious about VBA and Excel.

www.cpearson.com

John,
Thanks for your reply too. I appreciate the bit of explaination you
gave, it always helps bring clarity.