How can I create a macro that saves a worksheet as new file in a new
folder? To be more specifc. The main file has two worksheets. I want
worksheet two to be copied in a specific folder and the file to be
named the contents of a specific cell in worksheet 1.

I cant figure it out :(

Hi,

This copies the second sheet from the active workbook and saves it with a
filename from A1 of the first sheet. Right click any sheet tab, view code and
paste the code in and run it.

Sub Stantial()
Dim wb As Workbook
Mypath = "C:\MyDocuments\" 'Change to suit
Fname = Sheets(1).Range("A1").Value
Sheets(2).Copy
Set wb = ActiveWorkbook
p = Sheets(1).Range("A1").Value
wb.SaveAs Mypath & Fname & ".xls"
wb.Close
End Sub

Mike

" wrote:

How can I create a macro that saves a worksheet as new file in a new
folder? To be more specifc. The main file has two worksheets. I want
worksheet two to be copied in a specific folder and the file to be
named the contents of a specific cell in worksheet 1.

I cant figure it out :(

OOPS

This line isn't necessary, I put it there for debugging and forgot to delete
it

p = Sheets(1).Range("A1").Value

Mike

"Mike H" wrote:

Hi,

This copies the second sheet from the active workbook and saves it with a
filename from A1 of the first sheet. Right click any sheet tab, view code and
paste the code in and run it.

Sub Stantial()
Dim wb As Workbook
Mypath = "C:\MyDocuments\" 'Change to suit
Fname = Sheets(1).Range("A1").Value
Sheets(2).Copy
Set wb = ActiveWorkbook
p = Sheets(1).Range("A1").Value
wb.SaveAs Mypath & Fname & ".xls"
wb.Close
End Sub

Mike

" wrote:

How can I create a macro that saves a worksheet as new file in a new
folder? To be more specifc. The main file has two worksheets. I want
worksheet two to be copied in a specific folder and the file to be
named the contents of a specific cell in worksheet 1.

I cant figure it out :(

Sub Make_New_Book()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Sheet2").Copy
ActiveWorkbook.SaveAs Filename:="C:\testing" _
& "\" & Range("A2").Value
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Edit "C:\testing" to suit your path and folder name.


Gord Dibben MS Excel MVP

On Sat, 13 Dec 2008 02:05:53 -0800 (PST), wrote:

How can I create a macro that saves a worksheet as new file in a new
folder? To be more specifc. The main file has two worksheets. I want
worksheet two to be copied in a specific folder and the file to be
named the contents of a specific cell in worksheet 1.

I cant figure it out :(