Easy Macro help

S

sra5

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 :(
 
M

Mike H

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
 
M

Mike H

OOPS

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

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

Mike
 
G

Gord Dibben

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
 
Top