Hi,

I have encountered a strange and unexpected problem with a very simple macro which copies worksheets:

For x = 1 To y
Sheets("B").Select
Sheets("B").Copy After:=Sheets(24)
Range("N1").Value = x
ActiveSheet.Name = "" & x & ""
Next

This used to work fine, but now if I have a value of y greater than 27, I get the following error. (Before it starts copying the worksheet called "B", there were 24 worksheets in the Workbook, so it's not a problem of size - and it used to work with many more worksheets)


Run-time error ‘1004’

Copy method of Worksheet class failed



The only change I have made in the interim was to change some lookup functions on another sheet. They now refer directly to worksheets named "1", "2", "3', etc which are created by the macro, rather than through INDIRECT functions.

(As a side issue, do INDIRECT lookups significantly slow down a workbook?)


Why should it stop when it gets to 27? - any ideas?

Thanks

free