Excel Run time Error 1004: Copy method of Worksheet Class failed

I've spent all day writing code to copy a worksheet and manipulate data. I have just finish writing the code to get data from other sources. Now when I run the macros all together, the part of the code that copies the existing sheet fails. and I get the error code '1004'

Here is the code I'm using:

Please Login or Register  to view this content.

The individual macros (NewPPGDaten...) work fine on their own, its the first line: Sheets("PPG Daten").Copy After:=Sheets(9) thats giving me difficulty.

Any ideas?

*bump* anyone?

Hello chrismann85,

The code syntax is correct. The only possible reasons it would fail would be if you have less than 9 worksheets or you named the worksheet the number 9. In the case of the latter, it should appear in quotes - Sheets("9"). Barring these conditions, the problem would have to be elsewhere in your code.

Sincerely,
Leith Ross

Or it could be that annoying bug in Excel that still hasn't been fixed: http://support.microsoft.com/kb/210684.

The workaround described there is not overly inconvenient.

Why do you have 108 (or is it 109) different subroutines?

Hello Shg,

Thanks for sharing that article. I have never encountered the problem as described by Microsoft, but thanks to you I am now aware a problem does exist with copying a worksheet.

Sincerely,
Leith Ross

That link somehow seemed a lot harder to find when I was having the same problem a few years ago, Leith

I echo Norie's question ...

Thanks for all the helpful advice, for some reason, when I saved and closed the workbook, and came back to it in the morning it decided to work . Bit confusing but I'm not complaining.

I do have a lot of subroutines! it was a very confusing code to write, thats why I was so worried when then damn thing didn't run. Each one is similar to the last with minor things changed.