VBA EXCEL - BUG: Copy Sheet
Solved
nnaoy33
Posted messages
1
Status
Membre
-
Franck -
Franck -
Hello,
Hello everyone,
I have a small issue with one of my macros in a workbook:
The first workbook consists of a table from which I would like to distribute each row into sheets of a second workbook. (This workbook has as many sheets as my table has rows)
Here's how the macro works:
I created a hidden model sheet in the second workbook:
For each row, the macro copies my sheet, fills it, and moves to the next row. I've created a loop...
Unfortunately, this loop only works 56 times even though my table has 250 rows (at most).
The following error occurs:
Runtime error 1004
The Copy method of the Worksheet class has failed.
on this line:
Workbook.Sheets("Model").Copy after:=Workbook.Sheets("Home") 'Home being a home sheet in the second workbook
This line is indeed in the loop!!
- However, I haven't exceeded the maximum number of sheets allowed in a workbook. I can still add tabs manually.
- The model sheet only "weighs" 200KB
HELP !!
Thank you very much in advance
Yoann
PS: I hope I was clear enough. I can provide more information if needed, but I think the essence of the problem is here.
Hello everyone,
I have a small issue with one of my macros in a workbook:
The first workbook consists of a table from which I would like to distribute each row into sheets of a second workbook. (This workbook has as many sheets as my table has rows)
Here's how the macro works:
I created a hidden model sheet in the second workbook:
For each row, the macro copies my sheet, fills it, and moves to the next row. I've created a loop...
Unfortunately, this loop only works 56 times even though my table has 250 rows (at most).
The following error occurs:
Runtime error 1004
The Copy method of the Worksheet class has failed.
on this line:
Workbook.Sheets("Model").Copy after:=Workbook.Sheets("Home") 'Home being a home sheet in the second workbook
This line is indeed in the loop!!
- However, I haven't exceeded the maximum number of sheets allowed in a workbook. I can still add tabs manually.
- The model sheet only "weighs" 200KB
HELP !!
Thank you very much in advance
Yoann
PS: I hope I was clear enough. I can provide more information if needed, but I think the essence of the problem is here.
Configuration: Windows XP Internet Explorer 6.0
9 réponses
Here is what I found on the Microsoft site:
Cause:
This issue occurs because each time you copy the same sheet, a code name is assigned to the copy, and with each copy of the sheet, this code name becomes longer. For example, if you copy sheet1 once, the code name assigned to the sheet is sheet11. When you copy this sheet again, the name sheet111 is assigned to it, and so on. The maximum length of the code name is about 35 characters. When this limit is reached, Excel stops responding.
Proposed solution:
To work around this issue, try using code similar to the following to copy worksheets:
***
Sub Copy Sheets()
For x = 1 To 300
Sheets.Add
Workbooks("YourWorkBook.xls").Sheets("sheet1").Cells.Copy
ActiveSheet.Paste
Workbooks("YourWorkBook.xls").Sheets("sheet1").Select
Next x
End Sub
***
There you go...
Cause:
This issue occurs because each time you copy the same sheet, a code name is assigned to the copy, and with each copy of the sheet, this code name becomes longer. For example, if you copy sheet1 once, the code name assigned to the sheet is sheet11. When you copy this sheet again, the name sheet111 is assigned to it, and so on. The maximum length of the code name is about 35 characters. When this limit is reached, Excel stops responding.
Proposed solution:
To work around this issue, try using code similar to the following to copy worksheets:
***
Sub Copy Sheets()
For x = 1 To 300
Sheets.Add
Workbooks("YourWorkBook.xls").Sheets("sheet1").Cells.Copy
ActiveSheet.Paste
Workbooks("YourWorkBook.xls").Sheets("sheet1").Select
Next x
End Sub
***
There you go...
Hello,
Suggestion :
Example tested under Office 2003 (WXP).
Lupin
Suggestion :
Example tested under Office 2003 (WXP).
Option Explicit Sub Test() Dim Workbook As Workbook Dim Loop As Long Application.ScreenUpdating = False Set Workbook = ActiveWorkbook Loop = 1 Workbook.Sheets("Model").Copy after:=Workbook.Sheets("Home") ActiveSheet.Name = "Copy" & Loop For Loop = 2 To 250 Workbook.Sheets("Model").Copy after:=Workbook.Sheets(Workbook.Sheets.Count) ActiveSheet.Name = "Copy" & Loop Next Loop Application.ScreenUpdating = True End Sub ' Lupin
Hello,
I’m not sure about VB, but in VBA, there’s a very simple solution to avoid this kind of “bug”....
The principle:
You create a file containing only one sheet, this sheet serving as a template (you name this file “template.xls”, for example). This sheet obviously has the same structure as your Sheet 1.
Then, instead of copying Sheet 1 afterward, you insert a new sheet, of the same type as the “template.xls” file, with this line of code:
You can add as many identical sheets as you want....
This line of code replaces your line:
Have a nice day.
I’m not sure about VB, but in VBA, there’s a very simple solution to avoid this kind of “bug”....
The principle:
You create a file containing only one sheet, this sheet serving as a template (you name this file “template.xls”, for example). This sheet obviously has the same structure as your Sheet 1.
Then, instead of copying Sheet 1 afterward, you insert a new sheet, of the same type as the “template.xls” file, with this line of code:
Sheets.Add Type:=ActiveWorkbook.Path & "\template.xls", _ After:=Sheets(Sheets.Count)
You can add as many identical sheets as you want....
This line of code replaces your line:
xlSheet.Copy after:=xlBook.Worksheets(.Worksheets.Count)
Have a nice day.
Hello
- I still haven't exceeded the maximum number of sheets allowed in a workbook.
The maximum number of sheets in a workbook depends on the available memory...
I can also manually add tabs.
Surely your VBA procedure takes up memory that is then freed.
- The template sheet only "weighs" 200KB
That's the disk size, but in memory it's not necessarily the same.
You should check if you can optimize your procedure to avoid creating unnecessary or unreleased memory allocations.
If everything is optimal, you might need to see the RAM merchant...
--
always zen
- I still haven't exceeded the maximum number of sheets allowed in a workbook.
The maximum number of sheets in a workbook depends on the available memory...
I can also manually add tabs.
Surely your VBA procedure takes up memory that is then freed.
- The template sheet only "weighs" 200KB
That's the disk size, but in memory it's not necessarily the same.
You should check if you can optimize your procedure to avoid creating unnecessary or unreleased memory allocations.
If everything is optimal, you might need to see the RAM merchant...
--
always zen
Hello
First of all, thank you for getting back to me.
I have "lightened" my workbook containing the macro. I have removed my userforms and other unnecessary macros for its operation, so only the essentials remain.
Unfortunately, no additional sheets are being created.
I might have a solution, but I would like your opinion first: to start another loop from the 55th row of my table. A loop that would look the same.
Is that unnecessary? I will give it a try and keep you updated. I'm a beginner in programming, so it will take me a little while...
Yoann
First of all, thank you for getting back to me.
I have "lightened" my workbook containing the macro. I have removed my userforms and other unnecessary macros for its operation, so only the essentials remain.
Unfortunately, no additional sheets are being created.
I might have a solution, but I would like your opinion first: to start another loop from the 55th row of my table. A loop that would look the same.
Is that unnecessary? I will give it a try and keep you updated. I'm a beginner in programming, so it will take me a little while...
Yoann
Here I am again
So I tried to recreate the same loop for the remaining rows.
I had to reopen a 2nd workbook. However, it can only create 18 sheets in this new workbook.
I removed:
Application.ScreenUpdating
to free up memory.
But still nothing: the 2nd workbook is stuck at 56 sheets and the 2nd one at 18.
GRRRR.....
Any ideas?
Thanks in advance.
Yoann
So I tried to recreate the same loop for the remaining rows.
I had to reopen a 2nd workbook. However, it can only create 18 sheets in this new workbook.
I removed:
Application.ScreenUpdating
to free up memory.
But still nothing: the 2nd workbook is stuck at 56 sheets and the 2nd one at 18.
GRRRR.....
Any ideas?
Thanks in advance.
Yoann
Hello, I have a strange result when trying to copy a sheet with VBA in Excel 2000. On my workstation, everything goes as planned: inserting a copy of a sheet right after it and renaming it.
But on other workstations, the macro starts and... flies away somewhere without copying.
Apparently, the sheet selection works fine, but the copy request causes an exit from the procedure. For a few seconds, access to copy the sheet (via the right-click "copy and move" option) is not available on that sheet.
My workstation is equipped with Access. The others are not.
In the "tools" section of the Visual Basic Editor, it says in the checked modules: "missing CPSEtendedControls 1.0 type library."
All other programmed functions work well (calendar display, hiding or showing rows with a button, etc.).
But on other workstations, the macro starts and... flies away somewhere without copying.
Apparently, the sheet selection works fine, but the copy request causes an exit from the procedure. For a few seconds, access to copy the sheet (via the right-click "copy and move" option) is not available on that sheet.
My workstation is equipped with Access. The others are not.
In the "tools" section of the Visual Basic Editor, it says in the checked modules: "missing CPSEtendedControls 1.0 type library."
All other programmed functions work well (calendar display, hiding or showing rows with a button, etc.).
I would like to add that I then tried to record a simple sheet copy macro (on the computers where it doesn't work).
The recording goes well, but the macro behaves the same way as my program as soon as it encounters the term "copy".
The recording goes well, but the macro behaves the same way as my program as soon as it encounters the term "copy".
Hello everyone,
I’m using VB6 to communicate with Excel 2000. I have the same issue as you. Every time I get to the .Copy instruction, an Exception is triggered. I'm not trying to copy a sheet multiple times. This happens even for just a single instance. It's actually irregular. With the same code, sometimes it works and other times it gets stuck.
Here’s my code:
The solution from calborno seems interesting. I will test it and let you know. The one from Lupin.A looks similar to mine. It doesn’t work for me.
Best of luck!
--
C. Taha
I’m using VB6 to communicate with Excel 2000. I have the same issue as you. Every time I get to the .Copy instruction, an Exception is triggered. I'm not trying to copy a sheet multiple times. This happens even for just a single instance. It's actually irregular. With the same code, sometimes it works and other times it gets stuck.
Here’s my code:
... Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Set xlBook = frmTrain.xlBook With xlBook Set xlSheet = .Worksheets(1) xlSheet.Copy after:=xlBook.Worksheets(.Worksheets.Count) Set xlSheet = xlBook.Worksheets(.Worksheets.Count) With xlSheet .Name = "Train" & .Parent.Worksheets.Count Dim i As Integer ' Writing the train name .Cells(2, 3) = tempVar(0) ' Clear the cells .Range(.Cells(4, 2), .Cells(4, 3).End(xlDown)).value = 0 ' List of positions For i = LBound(tempVar(1)) To UBound(tempVar(1)) .Cells(4 + i, 2) = tempVar(1)(i)(0) .Cells(4 + i, 3) = tempVar(2)(i)(0) Next i End With End With Set xlBook = Nothing ...
The solution from calborno seems interesting. I will test it and let you know. The one from Lupin.A looks similar to mine. It doesn’t work for me.
Best of luck!
--
C. Taha
Indeed, the solution from calborno works very well. I would say on my side that the problem is solved.
You just need to know how to clear the clipboard at the end of the procedure (macro)!
Thank you all!
You just need to know how to clear the clipboard at the end of the procedure (macro)!
Thank you all!
I will now look for how to clear the clipboard at the end of the process (macro).
dim xlSheet as Sheet
set xlSheet = Sheets.Add