Excel VBa HELP - 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

 

[The Boosh!]

I doubt many people will be able to help you on here mate. You're probably on here already but there are some v. handy people on this forum:
http://www.excelforum.com/excel-programming/

 

[Sym0n]

Yeah I doubted it, but figured I'd spam it anyway lol. I'm not registered on there, but I did have a scan earlier whilst Googling everything I could through a flood of tears. I think I've found the answer and annoyingly it's due to a flaw in Excel 97-2003 and nothing at all to do with my code...so that's pissed me off even more, but at least I'm not feeling like a total r****d lol. I'll sign up anyway and give them a shot, never know they might be able to put the Microsoft KB article in to some sort of English that I can understand.

Cheers

 

[Sym0n]

May have got the answer from MrExcel forum...but can't test it on 2003 till the morning, rocking 2010 at home and it works perfectly on that so here's hoping!

For those interested...

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

 

[The Boosh!]

I love it when a piece of code comes together

 

[Sym0n]

It still amazes me how different people approach their VBa coding, the changes in the code above compared to my own for example. Delivers the same result (albeit his isn't encountering a flaw in Excel lol) and on the face of it share a lot of code but still they are totally different.

This little segment is only one piece of the code I have, the most important piece as everything depends on these copied sheets being available, but it will end up at least five times the size of that one segment when it's all put together and working how it should. And all this for something that people won't even appreciate PMSL.

 

[The Boosh!]

I know mate i get it at work all the time. I will write some code and get stuck on something, will pass it to the resident expert and he will come back with a completely different code.

I tend to put a lot of comments in my code, and if I have to scroll down then it's too long, so tend to open another module and call the code from a main module so i can keep track of whats doing what.

 

[Will.]

I'm surprised you didn't tell him to vLookup Luke

 

[The Boosh!]

Symon knows what he's on about, so i'd have told him to use nested index & match functions in favour of vlookups

 

[Sym0n]

I still need to start going back through the nine modules I have for this project that I've been working on for over a year (yep this is in essence just one workbook), tidy up the coding and comments, comment on things I didn't do at the time and break the modules down and use calls for them instead of the 12 page long (possible exaggeration) single subs that I currently have. Sadly this all needs time that I don't have and for senior management to stop making additions to the f**king thing and for them to understand that just because all they have to press is a button doesn't mean that it's that simple to make a spreadsheet do what it is. ARRRRRRRRRGH! And breathe...

I'm honestly impressed though with how much you have learnt in a few months Luke, less than a year ago you didn't even know about pivot tables and now you're coding complex VBa. We'll make a geek of you yet!

Cue Kieran to come along and start waffling about Unix and SQL... LOL

 

[Sym0n]

Boo. Not the way I planned my morning starting lol. Amended code still errors out on the same line as does every other persons code that I've been offered.

Looks like I'll have to try and make sense of this http://support.microsoft.com/default.aspx?scid=kb;en-us;210684&Product=xlw and incorporate it. Way to go Microsoft .

 

[The Boosh!]

It's a shame because excel 2003 executes code much more efficiently than 2007/2010. Bit gay when errors like this crop up.

 

[Sym0n]

I've given up with it now, TBH I've realised that I was over complicating a problem and have come up with another hopefully simplier solution.

Will find out if that works tomorrow morning...because at the minute I can't be arsed to even start to figure out why one line of code is stopping me being able to copy and paste between workbooks. Excel is pissing me off this week!

 

[amnesia]

Everything you ever wanted to know about Excel, and loads that you probably didn't...

Click Me