PDA

View Full Version : Solved: A macro to saveas a worksheet as a new workbook



abhay_547
04-22-2010, 02:13 PM
Hi,

I have the below macro which saves a sheet present in my workbook as new workbook but my problem is that i have multiple sheets which i want to save as new workbook when i run the below macro it goes into the new workbook while saving the worksheet as a new workbook so to come back to my main workbook I will have mention the name of my main workbook in my macro and i don't want to hard code the name of my main workbook in my macro code as it keeps on changing. I want a code will directly save the sheets of my main workbook as new workbook on a particular path and will not open them so that I don't need to come back / select my main workbook again and again (which is currently not possible because i can't hardcode the name of my main workbook in macro).

Note : I don't have all the worksheets currently in my main workbook. It works in this way.i.e when I run my main macro It generates some new worksheets in my main workbook and I want the macro to immediately copy and create a new workbook of a generated worksheet on a particular path.


Sub SaveWBS()
Sheets("Sheet1").Copy
ActiveWorkbook.SaveAs Filename:= _
"C:\My Documents\Book4.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End Sub


Thanks for your help in advance. :bow:

Bob Phillips
04-22-2010, 02:25 PM
Sub TestSaveWBS()

Call SaveWBS("Sheet1", "C:\My Documents\Book4.xls")
End Sub

Public Function SaveWBS(ByVal sh As String, ByVal fname As String)
Dim wb As Workbook
Set wb = ActiveWorkbook
wb.Worksheets(sh).Copy
ActiveWorkbook.SaveAs Filename:=fname, _
FileFormat:=xlNormal
ActiveWorkbook.Close
wb.Activate
End Function

abhay_547
04-22-2010, 02:40 PM
Hi Xld,

Thanks a lot. This is exactly what I wanted. :bow: