Consulting

Results 1 to 4 of 4

Thread: Solved: Save worksheet as new workbook, with macros disabled

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Regular
    Joined
    Mar 2009
    Posts
    11
    Location

    Solved: Save worksheet as new workbook, with macros disabled

    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:


    [VBA]
    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
    [/VBA]

    Thanks in advance!
    /Fred

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

  2. #2
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    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.

    [VBA]
    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
    [/VBA]
    If you uncomment the one line, it will close the new copy and leave the original workbook open.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  3. #3
    VBAX Regular
    Joined
    Mar 2009
    Posts
    11
    Location
    Thanks Lucas!
    This is just what I needed. Much thanks for your help.

    /Fred

  4. #4
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Glad to help Fred. Please mark your thread solved using the thread tools at the top of the page.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •