• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel 2010. Date and save issue

Guitman

New Member
Hi, I am using excel 2010. I am using a sheet with the =Today() function which is fine when I initially open the sheet. Is there a way that when I re-open the sheet (next day or next week, next month) that I can stop the date from updating to the current date?
Also I have a workbook with six sheets. One of the sheets is an invoice form and once the invoice is raised I would like to save this invoice sheet only to another workbook. Is this possible in excel?
Thanks for your help
 
Hi,

I don't think you can stop TODAY() function to get executed. If you change calculation to Manual than it can stop, but than all the calculations will stop.

Better to put a static Date in that cell.

Regards,
 
Hi,

I don't think you can stop TODAY() function to get executed. If you change calculation to Manual than it can stop, but than all the calculations will stop.

Better to put a static Date in that cell.

Regards,
Thanks for your reply. I thought that might be the case. What about the second point of my question regarding the saving of an individual sheet. Do you think this is possible. Thanks
 
@guitarman

See the attached sample file, this the invoice template available on Microsoft.
When you will press the Save Invoice button the invoice sheet will get saved on the desktop with the name as Invoice number.

Change the path of file before executing the macro.

Regards,
 

Attachments

  • Basic invoice1.xlsm
    28.7 KB · Views: 4
@guitarman

See the attached sample file, this the invoice template available on Microsoft.
When you will press the Save Invoice button the invoice sheet will get saved on the desktop with the name as Invoice number.

Change the path of file before executing the macro.

Regards,
Hi Somendra, sorry I have not been back to you but been in bed with flu. I have tried doing what you suggested but I keep getting the "debug" message every time I execute the macro. I am trying to save the invoice into a folder on my desktop. The folder is called "Invoice". I have attached the file I am using so that you can check my copy of the VBA file you sent.
Thanks
 

Attachments

  • PLUMBING MASTER QUOTE FORM copy.xlsm
    148.8 KB · Views: 4
Hi Somendra, sorry I have not been back to you but been in bed with flu. I have tried doing what you suggested but I keep getting the "debug" message every time I execute the macro. I am trying to save the invoice into a folder on my desktop. The folder is called "Invoice". I have attached the file I am using so that you can check my copy of the VBA file you sent.
Thanks
Hi ,

See if this works OK.

Narayan
Thanks Narayan, this seems to work ok. Only one point (if it can be done) Once I open the folder where the invoice has been saved the filename only shows as "14" instead of "Xbp-0014". Is this correct or is it something that can be fixed?
Thanks for your help
 
@guitarman

That's happening because your file name is in cell E2 and the content of cell is 14 which is formatted as Xbp-0014. The format is what you see but excel take the original content of cell to do the process on it.

If you change the content of the cell to Xbp-0014 it will get saved as that.

@NARAYANK991 ... Thanks sir for the backup.

Regards,
 
@guitarman

That's happening because your file name is in cell E2 and the content of cell is 14 which is formatted as Xbp-0014. The format is what you see but excel take the original content of cell to do the process on it.

If you change the content of the cell to Xbp-0014 it will get saved as that.

@NARAYANK991 ... Thanks sir for the backup.

Regards,
Ok I understand now. Thanks very much for your help
Joe
 
You are Welcome Joe!!!


Regards,
Hi Somendra, I am trying to expand on this workbook and I am trying to save another sheet called "Deposit Invoice". I am copying and pasting your VBA code that you supplied for the previous sheet and just changing the sheet name in the VBA code. Does not work. Could you please have a look at the attached file and let me know where I am going wrong? I am trying to automate this workbook as much as possible so once I can see how you go about these operations then I will try and do the rest myself. Thanks in advance.
 

Attachments

  • PLUMBING MASTER QUOTE FORM - Copy.xlsm
    176.9 KB · Views: 3
Hi,

If you are trying to save the invoice on Deposit Invoice than try changing this line

Code:
filen = "C:\Users\User\Documents\Excell 2010\Xcell Bathrooms\Invoice folder\Invoices Sent\" & Sheets("Invoice").Cells(2, 5) & ".xlsx"

to

Code:
filen = "C:\Users\User\Documents\Excell 2010\Xcell Bathrooms\Invoice folder\Invoices Sent\" & Sheets("Deposit Invoice").Cells(2, 5) & ".xlsx"

and remove this line:

Code:
ActiveSheet.Shapes.Range(Array("Button 1")).Delete

Try these changes and write back.

Regards,
 
Hi ,

I am not sure what you want to do , but the way the macro works is this :

1. You have a button on a sheet ; in the first version of the code , it is in the sheet labelled Invoice.

2. When you click this button , code is executed , whose first action is :

Cells.Copy

What this does is copy the active sheet , which is the sheet labelled Invoice.

Thereafter , the code creates a new workbook , pastes this copied data into the new workbook , and then saves the workbook under what ever name you have assigned it in the code.

Now , if you want that a different sheet should be copied and pasted into a new workbook , the first point is :

which button will you click ?

Is it the button on the sheet labelled Invoice , or is it going to be a new button which you will insert on the sheet labelled Deposit Invoice ?

Can you clarify ?

Narayan
 
Hi ,

I am not sure what you want to do , but the way the macro works is this :

1. You have a button on a sheet ; in the first version of the code , it is in the sheet labelled Invoice.

2. When you click this button , code is executed , whose first action is :

Cells.Copy

What this does is copy the active sheet , which is the sheet labelled Invoice.

Thereafter , the code creates a new workbook , pastes this copied data into the new workbook , and then saves the workbook under what ever name you have assigned it in the code.

Now , if you want that a different sheet should be copied and pasted into a new workbook , the first point is :

which button will you click ?

Is it the button on the sheet labelled Invoice , or is it going to be a new button which you will insert on the sheet labelled Deposit Invoice ?

Can you clarify ?

Narayan
It will be a new button on the Deposit Invoice sheet
 
Hi ,

In which case the suggestion given by Misra will work.

However , you need to be aware that your sheet tab labelled Deposit Invoice is is fact "Deposit Invoice " , with an extra trailing space.

Narayan
 
Hi ,

In which case the suggestion given by Misra will work.

However , you need to be aware that your sheet tab labelled Deposit Invoice is is fact "Deposit Invoice " , with an extra trailing space.

Narayan
Hi Narayan, firstly I can not put in an extra trailing space as it highlights in red.
Am I correct in what I am doing (as below)
What I am doing is creating a new macro and copying your VBA code into this macro and then modifying the line you suggested. The new macro name is creatInvoive2. Is this correct?
Is there any chance that you can modify the file I sent you so that I can read the VBA code and compare it with mine. I need to have a save button on both Deposit invoice and Invoice sheet saving to the path file I have shown in my file. I need to be able to save either of the sheets independently.
 
Back
Top