Hellpppp

[sarahgee]

Ok so looking for some serious help here... creating a new invoice for my dads business.. and ive set up a button bar to take him into 3 different spreadsheets depending on the payment method.

Now what i want to do is .. set a button on the for example cash spreadsheet to go to save as, and save it in the cash payment folder as the customers name?

SO this means once the invoice is filled in.. you would press the extremly large SAVE button .. which would then save as the invoice as the customers name ?

Im running on excel 2007. I really hope this is possible and not just made up in my little head somewhere?

Cant anyone give me a step to step on how to do it ! ive been messing around with Macros for a few hours and still cant get it right !

Any help ! please!!

 

[repairman615]

Hello,

Welcome to the Board!

What if there are more than one invoice for that customer? Perhaps you may like to have their name and the invoice number. The invoice number would make each invoice file saved unique avoiding a future snag.

If you could provide the cell address where the customers name is and also the cell address that houses the invoice number, I may be able to help with the code.

This is possible, also this is in your head too.

edit: Also the file paths to the folders.

 

[shadow12345]

My best guess but I assuming that the customer name is in A1 and the folder is in the C drive.

(I added the date and time of the save so you don't replace a file... but the invoice number is a good idea, just do the same as i did for customer name)

 Sub testsave()
 spath = "c:\cash payments\"
 custname = Range("a1").Value
        
        ActiveWorkbook.SaveAs Filename:=spath & (custname & Format(Now, "DD-MM-YY HH.MM") & ".xlsm")

    ActiveWorkbook.Close
 End Sub

 

[repairman615]

(I added the date and time of the save so you don't replace a file...

This is a good idea too. I was trying to think what could make each unique to avoid errors, without even once thinking about time or date.

I will remember time and date next time, Nice.

 

[tweedle]

Another version,
Saves just the worksheet to a seperate workbook and prompts for the name.

in a module...

Dim PaymentSheet As Worksheet
Dim CustName As String
Dim SavePath As String
 
Sub SaveCashReceipt()
Set PaymentSheet = Sheets("Pay Receipt") 'adjust sheet name as needed
CustName = PaymentSheet.Range("C3").Text 'adjust cell references as needed
vDateTime = Format(Now(), "_yyyymmdd_hhmm") 'Date/Timestamp for file name - optional, but will lessen chance of over-writing existing file(s)
SavePath = Application.GetSaveAsFilename(CustName & vDateTime, "Excel Files;*.xls,*.xlsx", 1, "Save Cash Receipt")
If SavePath = False Then
    MsgBox "Canceled-File not saved"
    Exit Sub
End If
PaymentSheet.Copy 'copies the worksheet to a new workbook
Workbooks(Workbooks.Count).SaveAs SavePath, xlDefault 'saves the new workbook
Workbooks(Workbooks.Count).Close True 'closes the new workbook
End Sub

 

[sarahgee]

Right i cant get this one to work it keeps telling me theirs a problem with this line

ActiveWorkbook.SaveAs Filename:=spath & (CustName & Format(Now, "DD-MM-YY HH.MM") & ".xlsm")

It prompts me to save it .. but it wont do it, I dont know whether its best just to make it the invoice number rather than the date and time? but i cant figure out how to do that ! ahh this is a night mare

 

[sarahgee]

Right i tried the other code...


and when i press save its saying it needs debugged... with this line

If SavePath = False Then

Any help trying to get this finished today ?

 

[tweedle]

is it giving an error message?
If so, what is it?

 

[tweedle]

I see it;
s/b "False", in quotes