File name varible problem

C

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.
 
J

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
 
C

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top