Save single worksheet as new workbook

I have a workbook containing multiple worksheets. I would like a macro
that would save a single worksheet as a new file. Also, can I have the
new file be saved as the data in a certain cell?

I have a macro that will save the worksheet to a new workbook, but it
saves it with the same filename each time.

Please help!

Thanks
-GorillaBoze


---
Message posted from http://www.ExcelForum.com/

Hi GorillaBoze

One way is to use a date time stamp in the file name
Example that save the activesheet in C:\

Or look in the VBA help for Application.GetSaveAsFilename

Sub CopySave_ActiveSheet()
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "C:\Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
.Close False
End With
Application.ScreenUpdating = True
End Sub

For a cell value use
..SaveAs Sheets("mysheet").Range("d1").Value



--
Regards Ron de Bruin
http://www.rondebruin.nl


"GorillaBoze " wrote in message ...
I have a workbook containing multiple worksheets. I would like a macro
that would save a single worksheet as a new file. Also, can I have the
new file be saved as the data in a certain cell?

I have a macro that will save the worksheet to a new workbook, but it
saves it with the same filename each time.

Please help!

Thanks
-GorillaBoze


---
Message posted from http://www.ExcelForum.com/

I am having problems when I try to use a cell value. I am trying to
use:

.SaveAs "Z:\SecDept\ Sheets("Tax40").Range("A10").Value
& " " & strdate & ".xls"

Is this correct?


---
Message posted from http://www.ExcelForum.com/

Hi,

Not quite:

Try:
..SaveAs "Z:\SecDept\" & Sheets("Tax40").Range
("A10").Value & " " & strdate & ".xls"


jeff

-----Original Message-----
I am having problems when I try to use a cell value. I
am trying to
use:

.SaveAs "Z:\SecDept\ Sheets("Tax40").Range("A10").Value
& " " & strdate & ".xls"

Is this correct?


---
Message posted from http://www.ExcelForum.com/

.

That works! Thanks Jeff

I have a question though...why does it not work when I add another
folder to the location?

.saveas "Z:\SecDept\Recommendations" & Sheets("Tax40").Range
("A10").Value & " " & strdate & ".xls"


---
Message posted from http://www.ExcelForum.com/

Use this

.SaveAs "Z:\SecDept\" & Sheets("Tax40").Range("A10").Value _
& " " & strdate & ".xls"


--
Regards Ron de Bruin
http://www.rondebruin.nl


"GorillaBoze " wrote in message ...
I am having problems when I try to use a cell value. I am trying to
use:

SaveAs "Z:\SecDept\ Sheets("Tax40").Range("A10").Value
& " " & strdate & ".xls"

Is this correct?


---
Message posted from http://www.ExcelForum.com/

saveas "Z:\SecDept\Recommendations\" & Sheets("Tax40").Range("A10").Value & " " & strdate & ".xls"

You forgot a \ after the folder name

--
Regards Ron de Bruin
http://www.rondebruin.nl


"GorillaBoze " wrote in message ...
That works! Thanks Jeff

I have a question though...why does it not work when I add another
folder to the location?

saveas "Z:\SecDept\Recommendations" & Sheets("Tax40").Range
("A10").Value & " " & strdate & ".xls"


---
Message posted from http://www.ExcelForum.com/