Save As then Close Workbook

yve

New Member
Joined
Jan 18, 2004
Messages
1
Hi everybody,

I am desperate to get this simple task sorted. I am new to VBA but somehow have managed to sort out some more complicated issues than this one but on this I am sooooo confused.

Basically, I have a spreadsheet that I open up and use as a blank form. I fill in the details and save it over itself so I have a copy of the last form information when I reopen it. It acts as a template as it has an autonumber that is used to generate an invoice number. So when the workbook is reopened the next invoice number is generated but the previous invoice details are in there just in case I forgot to print it.

What I also need to do is have a copy of each invoice saved as a seperate file so that I have a seperate copy of it but.... whenever I get help on the SaveCopyAs method, it seems to request that I have to put a filename in but I won't know what that is until I've done the invoice as I want to take the information from 2 fields within the worksheet i.e. Customer Name and Invoice Number cells.

If anyone can actually understand what the heck I am talking about I would be soooooo very grateful for any help.

So in Summary:-

I need to Save a copy of the open Workbook as a different name completely but the options have to be totally variable. CAN ANYONE HELP PLEASE?


Thanks a million.

Yve :oops:
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Not quite exactly what you are looking for but see if this helps you. Pick & choose what you need. If you need to specifiy paths, just add them to the front of the filenames.

Assumes Customer Name is in A4
Invoice Number is in D10

Code:
Sub saveboth()
    Dim fullnme
    Dim Cust
    Dim Invce

    '  save this workbook with it's original name to the default path
    ThisWorkbook.Save

    ' Will save as a different workbook, change the name of active workbook to
    ' the values of A4 & D10
    ',,,,,,,,True adds to MRU
    Cust = Sheets("sheet1").Range("A4")
    Invce = Sheets("sheet1").Range("D10")
    fullnme = Cust & Invce
    ThisWorkbook.SaveAs fullnme, , , , , , , , True

    ' OR to save a copy and keep the open workbook as same name

    Cust = Sheets("sheet1").Range("A4")
    Invce = Sheets("sheet1").Range("D10")
    fullnme = Cust & Invce
    ThisWorkbook.SaveCopyAs fullnme

    'close workbook with no further notifications
    Application.DisplayAlerts = False
    ActiveWorkbook.Close SaveChanges:=False
    Application.DisplayAlerts = True

End Sub

HTH
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top