Solved: Save on exit to a folder

[Darren]

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

[OBP]

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.

[Norie]

Darren

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

[Darren]

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

[mdmackillop]

[vba]Sub SaveSales()
Application.ScreenUpdating = False
Worksheets("Sales").Copy
ActiveWorkbook.SaveAs "C:\Finished Invoices\Sales.xls"
ActiveWorkbook.Close
Application.ScreenUpdating = False
End Sub
[/vba]

[daylefmp]

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.

[Darren]

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

[Darren]

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

[Darren]

Whoops VBA TAGS did not work

Could you mark this as solved PLEASE

Thankin you