ansaurus

Question

Excel 2007 - Sheets.add from template file loses formatting

Answer 1

+1 A:

Can you try adding your template worksheet in a different way? Try:

Sub AddSheet()

    Application.Workbooks.Add "Z:\Investments.xltm"
    ActiveWorkbook.Worksheets("SheetName").Copy After:=ThisWorkbook.Worksheets(1)

End Sub

This will create a new workbook based on the template, then copy the required worksheet to the calling workbook.

I'm just curious as to whether this makes a difference.

There is another option you may want to consider, if there's only one workbook that uses the template, is to have a hidden worksheet in your workbook and base new sheets on that.

2009-09-30 08:27:59

Yes it did. I had to throw in a line to activate the book I was copying into though as otherwise I was getting an application error. I have to add about 30 or so sheets this way (very dynamic template) so hopefully this does not slow it down. Thanks! What would life be like if you didn't have to work around Excel's shortcomings!

Codezy 2009-10-01 14:56:03

Just as an aside, I usually set a variable to point to the workbook I'm using, e.g.dim wkbk as Workbookset wkbk = ActiveWorkbookor set wkbk = ThisWorkbookThen I don't have to worry about which is the active workbook.In my example above, I would also close the template workbook after I had copied the worksheet, adding the lineActiveWorkbook.Close SaveChanges:=Falseso that I didn't have to worry about it any more.Getting Excel to do what I want is half the fun :)

2009-10-01 15:12:33

Agreed I do the same as Excel does like to bite you in the butt if you make any assumptions about what the activebook, sheet or cell might be.

Codezy 2009-10-14 19:24:08

Yup. It's one of those things I learned the hard way!

2009-10-15 10:02:03

I did discover one downside to this approach. If you have a formula that is 255 chars or longer, adding sheets this way truncates those formulas whereas the sheets.add type= way does not do that. The sad thing is now each way has a total showstopper downside (short of reworking all formulas over 255 chars)

Codezy 2009-10-15 20:33:49

Interesting. Thanks for the info.

2009-10-16 15:54:48

ansaurus

tags:

views:

answers:

Excel 2007 - Sheets.add from template file loses formatting

related questions