save worksheet to a folder

H

Hoyos

How can I save a worksheet from a workbook to another workbook and name it
date saved at using a vb code. And any additional sheet to be saved in the
same workbook.
My aim is to be able to save a stat sheet automatically once a week.
 
B

Bob Phillips

Sub SaveMyWorkbook()
Worksheets("Sheet1").Copy
ActiveWorkbook.SaveAs Format(Date, "yyyy-mm-dd") & ".xls"
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
R

Rick Rothstein \(MVP - VB\)

Add the folder's path in front of the Format statement. For example...

ActiveWorkbook.SaveAs "c:\temp\" & Format(Date, "yyyy-mm-dd") & ".xls"

Rick
 
B

Bob Phillips

No, if you want that, best to be specific

Sub SaveMyWorkbook()
Worksheets("Sheet1").Copy
ActiveWorkbook.SaveAs "C:\myFolder\mySubFolder\" & Format(Date,
"yyyy-mm-dd") & ".xls"
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Top