Adam, Bonzo,

Thanks for your postings on this thread (for which I take some
responsibility!) I posted basically the same question again yesterday
without further response. I hadn't realised that there were further postings
to my July posting, which were helpful. But I still have a problem. I have
tried copying the worksheet to a new workbook using

Sheets("Standard tenancy data").Copy

and then later on, having renamed the worksheet I used the line:

ActiveSheet.Move Befo=Workbooks(workbookname).Sheets("EndTenanci es")

I found that this worked for about 12/13 copies and then the infamous 'run
time 1004' error appeared, with the slight variation in the qualification:

'Move Method of worksheet class failed' as opposed to 'Copy Method ...'

Any further insights? I'm using Excel 2002 SP3.

TIA
Ron

"adam12+" wrote:


Bonzo,

Glad my post could be of some help to you. Sorry to hear it sounds
like you are still having problems. I'm not sure what exactly you are
trying to do, but to answer your question, when I _move_ the recently
copied sheets from the "temporary" workbook, that "temporary" workbook
closes (since all the sheets in that workbook are being moved). So, I
don't think the "temporary" workbook is still there.

I tried to recreate what you are doing and didn't have the same
problem. Are you choosing just the newly created (copied) worksheets
or all the worksheets? Obviously if you try to delete all the
worksheets in the workbook you may have problems, although in my
version of Excel, it just pops up a message box saying I can't do that
and nothing gets deleted and nothing closes.

What version of Excel are you using? Perhaps that's a factor.

Good luck. I hope you can post saying you've figured it out!


--
adam12+
------------------------------------------------------------------------
adam12+'s Profile: http://www.excelforum.com/member.php...o&userid=13417
View this thread: http://www.excelforum.com/showthread...hreadid=236159