My apologies

Please add .Name as follows:

myNewFile = application.activeworkbook.NAME ' The name of your file to
be
amended

The code should then do exactly what you require.
Coding formats for individual cells is a monster task, and would be
incredibly difficult to maintain.


"Robert Crandal" wrote:

Hmmm, I pasted your code into a subroutine, ran it, and then I
got the following error message:

"Run-time Error '438'
Object doesn't support this property of method"

BTW, yes, I am trying to immediately transform the entire spreadsheet's
cell formats (colors, fonts, size, text values, etc). I want my users
to be able to select 1 of 4 different layouts, so their current
spreadsheet will be customizable, kinda like changing the
"skins" of a WinAmp music player or something. So, the
design of the current spreedsheet will change, but the data in some of
the cells will not change.

I could code in ALL of the properties for ALL of my template designs,
but it seems like a lot of work. I am just wondering if there is an
easier way to do this and I thought templates would be a solution, but
I'm not sure?


"Paul" wrote in message
...
Do you mean overwriting the cells formats (colors, fonts, etc.) ?

If so, and if the data is in the same cells in each case :

myTemplateFile = "Template.xls" ' The name of the file containing
the required formats
myNewFile = application.activeworkbook ' The name of your file to be
amended

workbooks.open filename:=myTemplateFile

cells.copy
windows(myNewFile).activate
Range("A1").select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

workbooks(myTemplateFile).close (False)


.