Saving a worksheet to a separate file

[bigjim]

I've used this same code in the past and it worked, but its not working in
this workbook. Need some help. I'm using Excel 2003 and want save a
worksheet in my workbook as a separte file using a filename generated from
the worksheet. Here is the code I'm using:

Private Sub CommandButton6_Click()


Sheets("quicksilver asc").Select
Range("c8:c10").Select
Selection.Copy
Sheets("quicksilver asc f").Select
Sheets("quicksilver asc F").Range("c8:c10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False



Sheets("quicksilver asc f").Copy


Dim strappend As String
Dim strpath As String
Dim str3 As String

strappend = ActiveSheet.Range("l8")
strpath = "c:\field tickets\"
str3 = ActiveSheet.Range("b14")

fsavename = strpath & strappend & str3 & ".xls"

ActiveWorkbook.Sheets("quicksilver asc f").SaveAs fsavename
ActiveWorkbook.Close False

End Sub

 

[JLGWhiz]

Are you sure you didn't use SaveWorkspace?

 

[JLGWhiz]

You will probably have to create a new workbook, copy the sheet over to it,
then delete the sheet from the source document.

 

[RyanH]

This is the type code I would use. I got it to work for me.

Private Sub CommandButton6_Click()

Dim FilePathName As String

Sheets("quicksilver asc").Range("C8:C10").Copy
Sheets("quicksilver asc F").Range("C8:C10").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

' save worksheet with this file name
FilePathName = "C:\field tickets\" & Range("L8") & Range("B14") & ".xls"

With ActiveWorkbook
.Sheets("quicksilver asc f").SaveAs Filename:=FilePathName
.Close False
End With

End Sub

Hope this helps! If so please let me know and click "YES" below.

 

[bigjim]

You're ny hero again! I got it saved. The only problem is it also saved the
workbook into the same folder and closed the workbook. I would like to just
save the worksheet and be able to continue working in the workbook. I'll try
to figure that one out. If you have any suggestions, I would appreciate them.

Thanks again,

Bigjim

 

[bigjim]

No, I copied and pasted it just as it is. I got some help from Ryan, but
still not where I want to go. With his code, I was able to save the
worksheet, but it also closed out the workbook and saved it to the same
folder. I need to be able to save the worksheet and keep working in the
workbook. Any help would be apreciated.

Thanks,

Bigjim

 

[bigjim]

That won't work. I need to be able to just save the worksheet as a file in a
folder and keep working in the workbook. I will be creating seveal files
daily with this workbook and need to save the completed sheet as a new file
and then start over on another one.

thanks,

Bigjim

 

[JLGWhiz]

When you use the SaveAs, you are, in effect, renaming the current workbook
and saving it in one action. By closing the newly created workbook, you no
longer have either the original nor the new workbook open. However, both are
available in the folder. When I suggested creating a new workbook and
copying the sheet over to the new workbook, then save and close the new
workbook, I had in mind that the original workbook would remain open and you
could continue working. However, using Ryan's code, all you have to do is
re-open the original workbook, it is still available from file.