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 General
    Replies: 2
    Last Post: October 14th, 2010, 06:11
  2. Replies: 3
    Last Post: June 25th, 2007, 16:00
  3. Shared Workbook Saves As Different Filename
    By Bryony84 in forum Excel General
    Replies: 2
    Last Post: October 25th, 2006, 19:34
  4. Replies: 4
    Last Post: August 2nd, 2006, 20: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