Paste method fails when using template

**knobblywobbly** · 09-28-2011, 03:59 AM

Hi everyone,

I'm having a macro problem in Excel 2003 and wondered if you would be able to help me please? I'm copying some data from a worksheet, creating a new worksheet then pasting the data I copied into the new sheet. My code for this is below which works perfectly:

Please Login or Register  to view this content.

However, the data needs quite a lot of formatting so I created a template with the required formatting and tried to use this template when creating the new worksheet. Here's the code I'm now trying to use:

Please Login or Register  to view this content.

The above copies the data, creates the new worksheet based on the template file, renames the sheet to whatever is in the variable "shname", selects "A11" but then fails when trying to paste the data. I get the error message "Paste method of worksheet class failed".

Do you know why, just by changing the worksheet template, it would stop me from pasting the data into the new sheet?

Thanks

**Domski** · 09-28-2011, 05:45 AM

You could try something like this:

Please Login or Register  to view this content.

Dom

**knobblywobbly** · 09-29-2011, 02:43 AM

Thanks Dom,

That works,although as a complete beginner I don't understand how or why

**Domski** · 09-29-2011, 03:50 AM

I'm guessing that the opening of the template is clearing the clipboard so performing the copy operation after adding the sheet gets round the problem. I never tested it though.

Dom