Copy Method of WorkSheet Class Failed!

[Donna]

Has anybody solved this little puzzle then?
I have read a number of the threads all relating to my problem.....
Running a macro that is copying a Master Sheet then renaming that
sheet and repeating a number of times......and then erroring! The
master sheet contains a number of charts so is quite beefy to copy.
It tends to stop after 15 copies.

Is the answer to save and close after say 12 copies then
continue...does this work? or has anybody any other solutions? If you
have to save and close do you have to log out of windows to clear it's
memory out or can I just reopen and continue?

Any suggestions would be gratefully recieved.

Thanks All.

 

[Ron de Bruin]

Hi Donna

There is a example in this KB

Copying Worksheet Programmatically Causes Run-Time Error 1004
http://support.microsoft.com/default.aspx?scid=kb;en-us;210684&Product=xlw

 

[donna.gough]

Cheers....It looks like save and close is the fix.
I'll give it a go.

 

[donna.gough]

Next Question then folkes....
I'm sure this must be easy....
I will be using the same copying macro with a number of files so I want
to get the ActiveWorkBook filename into a string so I can open that
String Filename. Something along the lines of..

Filename= Get Active Workbook Filename Somehow
Application.Workbooks.Open (Filename)

Any ideas?

 

[donna.gough]

......c = ActiveWorkbook.FullName.....it was easy!

 

[Ron de Bruin]

Hi Donna

Do you want to select the files yourself or use the files in a folder

 

[Tom Ogilvy]

Why do you want to open the activeworkbook - it is already open.

 

[donna.gough]

To answer all the above questions....Tom, I want to open the
activeworkbook after it has closed to try and get round the Excel bug
or only being able to copy a particular sheet a number of times before
the above quoted error occurs.....and Ron...I am in the development
phase of this particular project and I tend to change the filenames to
wip1, wip2 etx quite reguraly so I can go back to earlier versions - so
thats why I want it to detect the filename before it closes so it will
open the right file without me having to change the code all the time.

The fix mentioned in Rons...microsoft/support link does not seem to
work. I could copy and paste about 50% more files than without it but
it still errors out on me. Thankfully I will only have to go through
this pain once as I am copying a master sheet containing charts which
are duplicated for the number of measured points in the file.
Once done (hopefully) they will not need to be generated again.

Any further suggestions would be grateful as I still have a great
number of charts to generate and this is going to be tedious task if it
is going to keep failing on me.

Cheers for all the above comments. Donna

 

[donna.gough]

Totally Baffled!..It comes up the copy error every 15th copy.
I've done this 3 times now (it copies 6, saves, closes then opens) and
even during third time I even copied the file from the network to the
local hard disk and continued the run from there and it still failed on
the 15th attempt....It's looking more like I'm going to have to log out
of windows every time!?!....help ..I've got alot of these to
do..........

 

[Tom Ogilvy]

Sub Copy Sheets
Dim sName as String, Icounter as Long
Dim oBook as Workbook
sName = Activeworkbook.FullName
set oBook = ActiveWorkbook
For iCounter = 1 To 275
oBook.Worksheets(1).Copy After:= _
oBook.Worksheets(oBook.Worksheets.count)
'Save, close, and reopen after every 2 iterations:
If iCounter Mod 100 = 2 Then
oBook.Close SaveChanges:=True
Set oBook = Nothing
Set oBook = Application.Workbooks.Open(sName)
End If
Next
End Sub

or to do it every time

Sub Copy Sheets
Dim sName as String, Icounter as Long
Dim oBook as Workbook
sName = Activeworkbook.FullName
set oBook = ActiveWorkbook
For iCounter = 1 To 275
oBook.Worksheets(1).Copy After:= _
oBook.Worksheets(oBook.Worksheets.count)
'Save, close, and reopen after every 1 iterations:
' If iCounter Mod 100 = 2 Then
oBook.Close SaveChanges:=True
Set oBook = Nothing
Set oBook = Application.Workbooks.Open(sName)
' End If
Next
End Sub