vba to save file as a new file and record data into existing data sheet

[lazyserv]

Hi there,

1. Here is what I want to do with my current excel file:
2. Click Save button to save all required data to another sheet
3. The number highlighted in reed need to be auto increment (+1) each time save button is click
4. Then the file will save as a new excel file with the form number (SRF-ABC-1001-14.xls)
5. And clear the current form and exit excel

Please refer my attached file for the sample what I want to do.

Please note the number highlighted in red should be auto increment number, once click save button, the file will automatic save as a new excel with the Form name.

Request Form.xlsx

Please note the number highlighted in red should be auto increment number.

Thank you so much for your help and time reading this.

[davesexcel]

Give this a go, change folder location to suite....

Please Login or Register  to view this content.

[lazyserv]

Give this a go, change folder location to suite....

Please Login or Register  to view this content.

I tried the code but I get sub error.

Anyway I have revised my question, can you please take a look as I provide more clearer what I want to achieve.

Also here is the attached excel file that got error.

I would like to learn how to code vba, therefore I hope you able to leave comment on the code above, provide one or two sample coding for what I want to achieve and I'll learn from there.

Thanks!

[Vikas_Gautam]

Try this code..

Please Login or Register  to view this content.

However You can also use SAVECOPYAS insted of SAVEAS, if you meant to save a backup...
Check the attached file..

[lazyserv]

Try this code..

Please Login or Register  to view this content.

However You can also use SAVECOPYAS insted of SAVEAS, if you meant to save a backup...
Check the attached file..

Hi there, I tested but it show me error.

Please refer attached excel form.xlsm

Apart from that, can you assist me by showing me how to store the data to the database sheet?

Basically this is a master form whereby all the new file name save will generate a copy of backup for future reference.

Also is it possible to make the module when click it save and clear all the content enter and exit this form?

Thank you so much for your help

[Vikas_Gautam]

The code is working for me...
May be you have to change the Folder in the code...

Regards,

[lazyserv]

The code is working for me...
May be you have to change the Folder in the code...

Regards,

Ok, how bout save the data in database sheet? do you able to advise what is the code need in order to save the current sheet data to database sheet data?

Thanks

[Vikas_Gautam]

what is meant by database sheet here...?

[lazyserv]

what is meant by database sheet here...?

Hi please refer below image:

\1

And the attached file which I highlighted in blue, how do I save the column data I want to the worksheet that name as Database?

form.xlsx

Thanks.