Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 2 of 2

Thread: SaveAs macro saves the whole workbook and not just sheets

  1. #1
    Join Date
    19th December 2011

    SaveAs macro saves the whole workbook and not just sheets

    Im sure its nothing more than a code line im missing. I'll try to explain what this macro is doing first and then what id like it to do. Bear in mind that as it is now is ok, it works but it doesnt work to the best efficency.

    When the user clicks the "Save Record" command button. This macro checks first to see if the cell b670 is filled, if not, it pops a message and asks to have the field filled and exits, if the cell is filled, then whatever is in the cell is what the filename will be. The same thing happens for cell B672 except that cell will be the sub directory under the default path. Once that is done it saves the work book, clears the 2 cells and, returns to the SAVED sheet. So here is what its missing. 1st, it saves the WHOLE workbook and id like it to save just the active sheet called 'Order Summary' and 1 other sheet called 'Order Entry'. 2nd Id like it to save them as a copy to the main workbook I am currenty working in and have it remain in that workbook rather than revert to the copy it just saved. I can attach a copy of the workbook to look at if that will help to understand the problem a little better, for now, here is the macro code:

    Sub SaveAs()
    If Range("B670") = "" Then
            MsgBox ("Please fill in Boat Number.")
            Exit Sub
        ElseIf Range("B672") = "" Then
            MsgBox ("Please fill in Company Name.")
            Exit Sub
    End If
    Dim strFilename, strDirname, strPathname, strDefpath As String
    On Error Resume Next ' If directory exist goto next line
    strDirname = Range("B672").Value ' New directory name will be whatever is put into this cell
    strFilename = Range("B670").Value 'New file name will be whatever is in this cell
    strDefpath = "C:\Users\Bench\Desktop\Order Entry\" 'Default path name
    If IsEmpty(strDirname) Then Exit Sub
    If IsEmpty(strFilename) Then Exit Sub
    MkDir strDefpath & strDirname
    strPathname = strDefpath & strDirname & "\" & strFilename 'create total string
    ActiveWorkbook.SaveAs.Copy Filename:=strPathname, _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    For Each Cell In Range("B670:B672")
    If Cell > 0 Then Cell.ClearContents
    MsgBox ("Record Saved.")
    End Sub

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    13th May 2006

    Re: SaveAs macro saves the whole workbook and not just sheets.

    the save as portion may be changed like this

    Sub test()
    Dim path As String, namefile As String
    path = "E:\excel\"
    'change the path to suit you
    namefile = "tempo"
    'change namefile to your wishes
    'tghe above is important code in this
    ActiveWorkbook.SaveAs path & namefile & ".xls"
    '.xls is for excel 2003 or earlier
    End Sub

    Excel Video Tutorials / Excel Dashboards Reports

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. SaveAs Macro saves to wrong directory
    By shatooi in forum EXCEL HELP
    Replies: 2
    Last Post: October 14th, 2010, 07:11
  2. Replies: 3
    Last Post: June 25th, 2007, 17:00
  3. Shared Workbook Saves As Different Filename
    By Bryony84 in forum EXCEL HELP
    Replies: 2
    Last Post: October 25th, 2006, 20:34
  4. Replies: 4
    Last Post: August 2nd, 2006, 21:24


Posting Permissions

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