ActiveSheet.Copy causing 1004 run-time error

[Sym0n]

Hi,
After spending the past seven and a half hours trying to get this to work I have admitted defeat.

What I'm attempting to do is copy 27 worksheets, names set in a range "nao_sheets", paste them in to the same workbook in any position (although a new workbook would also be fine) and then rename the copied sheet with the same name as the original worksheet but prefixed with the days date; i.e. 20110523 - Frontpage.

The code below works flawlessly for the first 15 worksheets, but then fails for any after with the error

Run-time error '1004':
Copy method of Worksheet class failed

Debug highlights the line shown in bold.

Sub testing2()
Dim cell As Range
Dim nao_Today As String
Dim nao_sheetname As String
nao_Today = Format(Date, "yyyymmdd")
For Each cell In Range("nao_sheets")
Sheets(cell.Value).Select
nao_sheetname = ActiveSheet.Name
[B]ActiveSheet.Copy After:=ActiveSheet[/B]
Sheets(cell.Value & " (2)").Select
ActiveSheet.Name = nao_Today & " " & nao_sheetname
Next cell
End Sub

I've tried to rename all the sheets to single characters, deleted sheets that could possibly be troublesome, different variations of the above code (especially the copying line), cried a little and sworn a lot. Sadly none of this has helped and the outcome has been exactly the same, to add to my misery I'm forced to use XP SP3 and Excel 2003.

Really grateful for any advice people can offer, before I tear out the little hair I have left.

Thanks!

Sy

 

[Andrew Poulsom]

Look here:

http://support.microsoft.com/kb/210684

 

[JackDanIce]

Welcome to the board

Maybe try:

Sub ParrotRacingDrivers ()
Dim cell as Range
Dim i as Integer
Dim nao_Today as String
nao_Today = Format(Date, "yyyymmdd")
For Each cell In Range("nao_sheets")
    nao_sheetname = cell.Value
    i = Sheets(cell.Value).Index
    Sheets(i).Copy after:=Sheets(i)
    Sheets(i + 1).Name = nao_Today & " " & nao_sheetname
Next cell
End Sub

 

[Sym0n]

Look here:

http://support.microsoft.com/kb/210684

Thanks, I read that earlier through a flood of tears and expletives whilst searching Google for an answer but didn't think it was totally relevant to my situation. Although admittedly I didn't fully understand the article and still don't lol.

Welcome to the board

Maybe try:

Sub ParrotRacingDrivers ()
Dim cell as Range
Dim i as Integer
Dim nao_Today as String
nao_Today = Format(Date, "yyyymmdd")
For Each cell In Range("nao_sheets")
    nao_sheetname = cell.Value
    i = Sheets(cell.Value).Index
    Sheets(i).Copy after:=Sheets(i)
    Sheets(i + 1).Name = nao_Today & " " & nao_sheetname
Next cell
End Sub

You sir could be my saviour, it's working perfectly for me at home but I'm on 2010 here and I've no way of getting hold of 2003 (or want to if I'm totally honest) to test it. So I'll have to hold off offering you my first born until the morning but I'm at least feeling more confident than I did earlier!

Thanks!

P.s. I'm loving the ParrotRacingDrivers sub name LOL

 

[JackDanIce]

It should work fine on Excel 2010, it's not doing anything particularly challenging or complicated, unless the syntax for coding has changed dramatically. Will wait to hear tomorrow if it works; glad to have helped

 

[Sym0n]

Morning,
I've tried the code and whilst it works for 2010 it's still erroring out on 2003 with 1004 on the line

Sheets(i).Copy after:=Sheets(i)

Which is basically the same as in my own code.

From what I can figure out from the link that Andrew posted I need to now cycle my workbook (or a new workbook) through a save and re-open process after 10 or so worksheets have been pasted. So it's back to the drawing board...as I try to figure out how to do that lol.

Thanks again for the help though.

Sy

 

[JackDanIce]

Shame, thought if I changed your code to avoid using .Activate or .Select it might solve the problem. Generally speaing, .Activate and .Select slows your code down and isn't something that's recommended and usually can be avoided. Good luck solving your problem though

 

[Sym0n]

Here's a fun fact, it turns out that even when manually copying and pasting these worksheets this problem still affects me. Copying and then renaming the first 15 worksheets works as expected but when I come to copy the 16th...nothing. Save, close and reopen the workbook and then I can continue as before.

 

[JackDanIce]

One other reason might be the values on Sheet1 do not exactly match the names of your worksheets

Try running this to get the names of all your worksheets:

Sub ListMe ()
Dim i as Long
Application.ScreenUpdating = False
With Sheets(1)
  For i = 2 to Worksheets.Count
    .Range("C" & i) = Sheets(i).Name
  Next i
End With
Application.ScreenUpdating = True
End Sub

Change the red C above to whatever empty column you want the values to go into and then compare that list against your original to check if the sheet names exactly match or not.

There's a text function called EXACT that you can use to compare two strings to test if they are exact matches or not.