Add date to worksheet template automatically

ilcaa

Well-known Member
Joined
May 25, 2005
Messages
686
Hi, i created a template worksheet and cant figure a code on automatically adding the current date in a "March 14" format when I insert the template to my current workbook...

any help would be appreciated , thanks. Max
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hello ilcaa,
I'm not sure what exactly you mean by "insert the template to my current workbook" (you
mean copy it from somewhere & paste it into the current Wb?) but here are a few ways to get
the current date.
In a cell somewhere (I would imagine in your Template Wb) you can use the formula: =TODAY()
Bear in mind that this must be copied & PasteSpecial'ed as Value or it will update to the
current date whenever the Wb gets opened.

Another option might be to use vba at some point, in which you could use:
Sheets("Sheet1").Range("A1").Value = Date
This will insert the date as a static value which will not update in the future once it's entered.

[Edit:]
Corrected a couple bonehead typos.
Hey Smitty! How's things? :biggrin:
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
How arre you adding the template to the existing wb? Does the date need to be static or dynamic?

What's your existing code?

Smitty
 

tvkrys

Board Regular
Joined
Jan 23, 2006
Messages
135
Max -

"ActiveCell = Date" enters the date when you run the macro and it will not automatically update to new date later. I use it alot so have it associated with a toolbar button and copied/pasted the equivalent icon from Word. But to automate it, try simply adding code to select the appropriate cell and then the above to your current code...

Example, if date to go in D3:

Range("D3").Activate
ActiveCell = Date

Hope this works for you,
Krys
 

ilcaa

Well-known Member
Joined
May 25, 2005
Messages
686

ADVERTISEMENT

thanks for everyones reply..

I basically right click on the current wb sheet tab and click "insert". my template box shows my templates and i choose the template I want...

my goal is....when that template gets inserted into my current wb, its contains the current days date (March 14) format, and i dont need to rename that sheet...

i think this makes more sense. thanks.
 

tvkrys

Board Regular
Joined
Jan 23, 2006
Messages
135
Max -

Didn't notice that you stressed a March 14 format... IF the template is already formatted that way and it doesn't translate that way when you paste it in to the new sheet, you need to add one more line of code.

Range("D3").Activate
ActiveCell = Date
Selection.NumberFormat = "mmmm d"

Does that work for you?
Krys
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
The object of interest (in this case the range D3) seldom if ever really needs to be activated
or selected in your code. (Code will run smoother & faster if we don't select/activate
the objects, but just refer to them instead.)

for example:
Code:
With Range("D3")
  .Value = Date
  .NumberFormat = "mmmm d"
End With
 

Forum statistics

Threads
1,136,926
Messages
5,678,605
Members
419,775
Latest member
joh93

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top