-
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
-
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.
-
Darren
You can copy a single worksheet to a new workbook like this.
[vba]
Worksheets("Sales").Copy
[/vba]
-
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
-
[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'
-
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.
-
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
-
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
-
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
-
Forum Rules