Copy a sheet into a new book and save file name as the current date.

Can anyone help with this?

I have an excel file which I use when I book keep journals in work. Each time I come across a new piece of bookkeeping I add to the file, name the sheet and index it. (I've attached a loose example for illustration) so the bookkeeping template.xls gets bigger every day.

Currently, when I book keep on a particular day, I open the template.xls workbook;

draft my journals;

and then manually copy the worksheets I have completed during the day from the template.xls workbook and paste the sheets into a new book and save the days bookkeeping as the current date.


With the file I have attached can someone show me how to write a macro and apply it to each of the buttons on the various work sheet tabs (each button will run the same macro).

Upon clicking a button, I would like the macro to;

Copy the active sheet into a new book and save the file as the current date.

Each subsequent click of a button the macro will check if the .xls file exists for the current date, if it does, then it will just copy the active worksheet to the file that has already been created.

In the new file which is saved as the current date, I need to remove the macro button and the hyperlink that reads back to index.

I have had some helpful macro's provided from another forum, however, the other forum does not appear to enable users to upload files, so I can't quite convey what it is I am trying to achieve.

I'd be for any help.
Thanks,
Jay3

Thanks for this!

My computer doesn't seem to like the 2nd line of the code?

Please Login or Register  to view this content.

Is it because I'm working on a Mac?

I can't get this Macro to work? Can anyone help me with it?
Thank you!

Hi there,

I'm now using your code in Excel 97 with Windows Vista, and it doesn't seem to like it either?

Is it because of the version?

Thanks

Thanks, but I've got that line to work now but it falls over at activesheet.copy.

This is the code I now have.

Please Login or Register  to view this content.

This actually sort of works now, but there's a couple of things.

When I click the button one it creats the worksheet and saves, then closes it as the current date.

I actually want to click the buttons as and when I complete each journal,

So I need the workbook to stay open and I would need code to tell the macro to check if the workbook (saved as the current date) actually exists.

If it does exist then subsequent clicks of the macro button should just copy the active sheet into the newly created book.

The current macro tries to overwrite the file on subsequent clicks and then falls over.

Any suggestions?

Thanks

Hello,

There are no errors now based on this code;

Please Login or Register  to view this content.

However, the macro needs to be amended as it's not quite what I need.

I need the code to be modified to do the following;

When the macro is executed for the first time (i.e. the first click of the button)
Check if the workbook for the current day has been created, and if it hasn't create it and save it. (That works fine, however, can we leave the new workbook open as opposed to closing it?)

On subsequent clicks of the button.The macro needs to copy and additional journals completed that day to the workbook which has already been saved, as opposed to trying to overwrite the existing file.

In any given day I will use the "Workbook Template.xls" workbook to produce 10 or more journals, so I want to be able to click the save button as I go.

I'd imagine the Macro will also need to check to the workbook which it creats is still open and if not open it prior to copying a worksheet accross.

I do not need the macro to close the new workbook each time.

Does this make sense?

Thanks for your help.
Jay

Fantastic!!!!

Thanks very much for your help it works perfectly!

Thank you!

Oh....one last thing.

This is the path I need to save my bookkeeping to

Please Login or Register  to view this content.

Can you tell me what line I need to change in this code to do this?

Please Login or Register  to view this content.

Thanks again,
John

Hi Leith,

Apologies I didn't see that the thread has now gone onto a second page.

I've made the amendment you mentioned and it save to the correct location, however, subsequent clicks of the macro button now cause excel to want to reopen the file (saved as todays date) and the macro doen't perform as did.

Excel flashes up a message to say, the file is already open and reopening etc etc....

Any suggestions.

Hi Leith,

I spotted the filename .xls bit and omitted it, however, I'm still getting this problem?

I'm on excel 97, could that be why?

Thanks,
John

This is the code now, tell me if I've inserted it in the wrong place.

PS - I also set Filepath as string, not sure if this is correct?

Please Login or Register  to view this content.

Hi Leith,

Can't email, but I've attached a template.

Thanks

Hi Leith,

Any joy with this?

Thanks
John