Excel 2003 VBA Copying Formatting Problem - PLEASE HELP!!

[krayten]

Hi,

New to this and pulling my hair out!! Would really appreciate it if
some kind sould could put me out of my misery with this. It might
actually be quite simple - but I'm missing it.

I have a template sheet which contains everything I need ( cell
formatting, spacing, a calendar object etc etc ). Each time my user
wants to add a new sheet I am making this sheet active and selecting
and copying a large range as so:-


Application.Worksheets.Add
ActiveSheet.Name = newsheet
Worksheets("template").Activate
Range("A1:AA20000").Select
Selection.Copy
Worksheets(newsheet).Activate
ActiveSheet.Paste

Then I make the newsheet active and paste the range in. Bet you can
probably guess what's going wrong though...all my formatting and the
calendar object is missing - it looks awful.

There must be a way ( please!! ) to copy the entire sheet including
formatting and the calendar object? Please, please help if you can....I
am brand new to this and very keen to learn more.

Thanks very much!

 

[Tom Ogilvy]

Just copy the sheet

Worksheets("template").Copy After:=Worksheets(worksheets.count)
Activesheet.Name = "NewName"

 

[krayten]

Beautiful!!
Just what I wanted...I'm ploughing my way through
"Excel VBA for Dummies" right now, fighting every inch of the way to
get
my application up and running.

Thank you Tom - It now does EXACTLY what I wanted.