Consulting

Results 1 to 9 of 9

Thread: Solved: Save on exit to a folder

  1. #1
    VBAX Regular Darren's Avatar
    Joined
    Feb 2005
    Posts
    98
    Location

    Solved: Save on exit to a folder

    Hi all

    (no clue how too)

    I have a master file called Nursery.xls. It is an invoice program. On the invoice at the bottom is a button "Process To Journal" this macro works well and the info is copied to the Sheet called "Sales Journal" in the same work book.

    What i am try to do with the following code is save a copy of the invoice to a separate folder called "Finished Invoices" With the information from cell B8 this is the name of the company and cell F17 which is the invoice number.

    [VBA]
    'on process to journal save a file as customer name and invoice number
    Dim FName As Variant

    FName = Sheets("Sales").Range("B8").Text & Sheets("Sales").Range("F17").Text & ".xls"
    If FName <> False Then
    If FName <> ActiveWorkbook.Name Then
    On Error GoTo Error_SaveAs
    ActiveWorkbook.SaveAs Filename:=FName
    Else
    ActiveWorkbook.Save
    End If
    End If
    Exit Sub

    Error_SaveAs:
    MsgBox "error executing SaveAs with filename = " & FName & vbCrLf & _
    "no save done", vbCritical
    [/VBA]

    If i run this it renames my the Nursery.xls file which is my master document, and saves the document with the correct file name in MY DOCUMENTS.

    What i would like is for it to just put a copy one sheet called "sales" into the "Finished Invoices" folder and save Nursery.xls with the new info that it has in the Sales Journal.

    It must not copy all the sheets in the workbook only "Sales"

    Nursery.xls contains 4 Worksheets called

    Sales/Sales Journal/Customers/Database


    The purpose of the Nursery.xls file is to create the invoice, process the info to the "Sales Journal" so i can keep track of who has been invoiced.

    I hope i explained well enough

    Kindest regards

    Darren
    South Africa

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,189
    Location
    Darren, I can't find a direct VBA method of just saving a single sheet as an Xls workbook.
    You could save the whole workbook and then delete the other sheets.
    You cold save the current workbook as it is, then delete the unneeded sheet and save it again with the new name.
    You could open a new workbook and copy the sheet to it and then save it.
    Or you could when on the correct sheet save it as a .csv file, which can be opened by excel. It only saves the sheet that you are on and you should be in cell A1 when you save it.
    ps this sounds like it should be an Access database.

  3. #3
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Darren

    You can copy a single worksheet to a new workbook like this.
    [vba]
    Worksheets("Sales").Copy
    [/vba]

  4. #4
    VBAX Regular Darren's Avatar
    Joined
    Feb 2005
    Posts
    98
    Location
    Hi OBP and Norie

    Thanks for the prompt response

    Norie if i wanted to copy the "Sales" sheet to a folder called Finished Invoices" located on the C:\ drive what would the code be

    Kindest regards

    Darren
    South Africa

  5. #5
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    [vba]Sub SaveSales()
    Application.ScreenUpdating = False
    Worksheets("Sales").Copy
    ActiveWorkbook.SaveAs "C:\Finished Invoices\Sales.xls"
    ActiveWorkbook.Close
    Application.ScreenUpdating = False
    End Sub
    [/vba]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  6. #6
    VBAX Newbie
    Joined
    Nov 2005
    Posts
    1
    Location
    Hi... This is something I do often in my code, and I just wanted to point out that you may want to change any of the formulas to values in your new workbook, so that they are preserved when the original Nursery.xls changes.

  7. #7
    VBAX Regular Darren's Avatar
    Joined
    Feb 2005
    Posts
    98
    Location
    Hi Daylefmp and Md

    Thanking you with the quick response

    Malcolm you always seem to be there for me no matter were i post and for that i thank you and you comprehensive outlook to peoples problems.

    i will add this line to the "Process to Journal" Button and will inform you as to the out come

    Thanks Guys


    darren
    South Africa

  8. #8
    VBAX Regular Darren's Avatar
    Joined
    Feb 2005
    Posts
    98
    Location
    Hi all

    Thanks for the help final code works well as follows

    [vba]
    'on process to journal save a file as customer name and invoice number
    Dim FName As Variant

    FName = Sheets("Sales").Range("B8").Text & Sheets("Sales").Range("F17").Text & ".xls"
    If FName <> False Then
    If FName <> ActiveWorkbook.Name Then
    On Error GoTo Error_SaveAs
    ActiveWorkbook.SaveAs Filename:=FName
    Else

    End If
    End If
    Exit Sub

    Error_SaveAs:
    MsgBox "error executing SaveAs with filename = " & FName & vbCrLf & _
    "no save done", vbCritical

    ActiveWorkbook.SaveAs "C:\Finished Invoices"
    ActiveWorkbook.Close
    Application.ScreenUpdating = True

    End Sub 'end of runBtn_Click() function
    [/vba]

    Well now on to next problem VAT Summary of all invoices by month

    well I sure think i will be back

    Kindest regards


    Darren
    South Africa
    PS.. do i get a one KB entry for this ??? LOL

  9. #9
    VBAX Regular Darren's Avatar
    Joined
    Feb 2005
    Posts
    98
    Location
    Whoops VBA TAGS did not work

    Could you mark this as solved PLEASE

    Thankin you

Posting Permissions

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