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.
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.