PDA

View Full Version : Solved: Save worksheet as new workbook, with macros disabled



fredL
02-22-2010, 06:18 AM
Hi everyone,

I have a macro I've been using to rename a workbook with cell values as the file name, and saving it a specific folder. It works great, but I've realized I need to update it a bit, and I'm hoping for your help.

The workbook has four worksheets (sheet1, sheet2, sheet3 and sheet4), and I'm looking for a solution that will save sheet1 as a separate workbook, preferably with all macros disabled. I would like to use the same file naming as in my old macro, which is as follows:



Sub savereport()

Dim myName As String
myName = "C:\SavedReport\"
myName = myName & Application.Cells(5, 1) & "_"
myName = myName & Application.Cells(5, 2) & "_"
myName = myName & Application.Cells(7, 3) & "_"
myName = myName & Application.Cells(5, 3) & ".xls"

ActiveWorkbook.SaveAs Filename:=myName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

file_name_saved = ActiveWorkbook.FullName
MsgBox "The report has been saved as: " & vbCr & vbCr & file_name_saved

End Sub


Thanks in advance!
/Fred

Edit Lucas: Select your code when posting and hit the vba button to format your code for the forum.

lucas
02-22-2010, 08:36 AM
This will make a copy of just sheet1 and save it as a workbook with just one sheet, ie sheet1. Unless there is code in the sheet1 module, no code will be in the new workbook.


Sub SaveSheet1()
Dim myName As String
Dim file_name_saved As String
Application.ScreenUpdating = False
myName = "C:\SavedReport\"
myName = myName & Application.Cells(5, 1) & "_"
myName = myName & Application.Cells(5, 2) & "_"
myName = myName & Application.Cells(7, 3) & "_"
myName = myName & Application.Cells(5, 3) & ".xls"
Worksheets("Sheet1").Copy
ActiveWorkbook.SaveAs Filename:=myName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
' ActiveWorkbook.Close 'uncomment to close the copy and leave original open
Application.ScreenUpdating = False
End Sub

If you uncomment the one line, it will close the new copy and leave the original workbook open.

fredL
02-23-2010, 03:37 AM
Thanks Lucas!
This is just what I needed. Much thanks for your help.

/Fred

lucas
02-23-2010, 09:11 AM
Glad to help Fred. Please mark your thread solved using the thread tools at the top of the page.