VBA to copy sheet to new workbook, save as new name with the days date, and...

Good morning happy campers,

I have a workbook (among many) it contains various sheets with command buttons to open, modify, save & close various other sheets.

Current standing;-
Work Book named "Updated Sheet"
Saved in location - "W:\LOGISTICS\Supervisor's\Housekeeping\Updated Sheet.xlsm"
On this workbook i have 6 tabs (some hidden some not), tab named "H.K Template" - on this tab i have a button that will set the print area to as many cells contain info, and a button to move (switch sheet) to another location.
I would like a code that will;
*Copy the "H.K Template" sheet ONLY from this workbook
*Paste into new workbook
*Delete all other tabs (so only the "H.K Template" is present)
*Save as the name of the user logged into windows, and the days date - "Joe.Bloggs 30.06.12" into a location (to be defined later, so for now use address above)
*Return to the workbook "Updated Sheet"
*Clear cell contents all previously entered into ranges "C3:J23" & "A25:J33"
Then save.

Now, i have some experience with VBA, the sheet im using has buttons for opening new sheets, auto filtering etc. which i can just about understand well enough but i cant get my head round saving as new seperate sheet and using the name of the person logged into windows and the date.
All help is appreciated

Kind Regards, Galvinpaddy

Hi Thanks for your input!
An issue i encounter - i cant find where it saves
I set the directory as "W:\LOGISTICS\Supervisor's\Housekeeping"
but it is not there, yet when i save it again, it warns me it already exists

Please Login or Register  to view this content.

What (if anything) have i done wrong in the above?

Sry, moment of idiocy ( i think) but it now appears

Thank you very much indeed

As a hopefully final question to this thread - in my OP, the second part - i have added to your code -

Please Login or Register  to view this content.

to clear all the contents, can i tidy this up or should this be ok?

Stuff of legends, thanks very much indeed.
Well deserved REP added

galvinpaddy

Originally Posted by john55

as an option for first question..

Please Login or Register  to view this content.

Hi John, if i could ask what the following line in your code does??

Please Login or Register  to view this content.

haha, lovely thanks