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

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

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" 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

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.


--
Casey


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