Solved: Duplicate Sheets with VBA - Error cannot complet task

[PJC]

I have a workbook with code, see below, to create duplicate sheets.


Sub copies()
Dim ws As Worksheet
Set ws = ActiveSheet
For i = 1 To InputBox("Enter number of copied")
ws.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Sheet" & Sheets.Count
Next

Set ws = Nothing
End Sub

I've run this to create an additional 18 worksheets but have only created 16 as now have an error message, as follows:-

"Excel cannot complete this task with available resources. Choose less data or close other applications"

Is this a "local" problem or is there a way to overcome as I only need another 2 worksheets.:banghead:

Thanks, Peter.

[lucas]

this ran for me repeatedly without error....could some of your other code be using resources?

[lucas]

You should use Option explicit and dim i

[PJC]

Hi Lucas,

Yes, there are a couple of other pieces of code per worksheet, so suppose this is taking up resources.

Are you suggesting I amend the duplicate sheet coding?

regards, Peter.

[lucas]

Depends on what it is.
As to the amending, I just suggest you use option explicit one time at the top of your module and dim i as an integer or variant....

Option Explicit
Sub copies()
Dim ws As Worksheet
Dim i
Set ws = ActiveSheet
For i = 1 To InputBox("Enter number of copied")
ws.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Sheet" & Sheets.Count
Next

Set ws = Nothing
End Sub

When your posting code be sure to select it and hit the vba button on the editor toolbar and it will enclose the code in tags for easier reading.

[johnske]

If the only sheets in the workbook are (say) sheets 3 and 4 and you ask for (say) 5 copies, you will get errors. Try this...

Option Explicit

Sub copies()

Dim i As Long, N As Long

For i = 1 To InputBox("Enter number of copies")
ActiveSheet.Copy After:=Sheets(Sheets.Count)

If Not WsExists("Sheet" & Sheets.Count) Then
ActiveSheet.Name = "Sheet" & Sheets.Count
Else
For N = 1 To Sheets.Count
If Not WsExists("Sheet" & N) Then
ActiveSheet.Name = "Sheet" & N
Exit For
End If
Next N
End If

Next i
End Sub

Public Function WsExists(WsName As String) As Boolean
'//Worksheets only\\
Dim Sheet As Worksheet
For Each Sheet In Worksheets
If Sheet.Name = WsName Then
WsExists = True
Exit For
End If
Next
End Function

[PJC]

Thanks everyone, now fine.:thumb

[mdmackillop]

If the only sheets in the workbook are (say) sheets 3 and 4 and you ask for (say) 5 copies, you will get errors. Try this...

Thanks John.