Save single worksheet as new workbook

G

GorillaBoze

I have a workbook containing multiple worksheets. I would like a macr
that would save a single worksheet as a new file. Also, can I have th
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 i
saves it with the same filename each time.

Please help!

Thanks
-GorillaBoz
 
R

Ron de Bruin

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
 
G

GorillaBoze

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

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

Is this correct
 
J

jeff

Hi,

Not quite:

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


jeff
 
G

GorillaBoze

That works! Thanks Jeff

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

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

Ron de Bruin

Use this

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

Ron de Bruin

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

You forgot a \ after the folder name
 
Top