Copying/Renaming Sheets (Excel 2000)

Posted by T.Y. on April 24, 2001 12:42 PM

Please help me with this little annoying problem that I got when I upgraded from '97 to the 2000 version.

My problem is when I copy and rename an existing worksheet, I could only get between 30-45 sheets. This happens whether I copy one sheet at time or multiple sheets with vb codes. So far my only workaround is to save my workbook, exit Excel, then repon the workbook in order to create the next batch of sheets.

Thanks in advance for any help that you can give...!

Posted by Jerid on April 24, 2001 1:32 PM

Hi TY

It sounds like the problem I'm running into in Excel97. Microsoft says that it's fixed in 2000, I guess not.

I have not been able to find a work around yet. Sorry.

Jerid

Posted by Dave Hawley on April 24, 2001 4:38 PM

Hi T.Y

You should be able to modify this code to suit.

Sub CopySheets()
Dim Ssheet As Worksheets
Dim IsheetCount As Integer
' Written by OzGrid Business Applications
'www.ozgrid.com

'''''''''''''''''''''''''''''''''''
' Copies X number of sheets
''''''''''''''''''''''''''''''''''

IsheetCount = Sheets.Count
For i = 1 To IsheetCount
Ssheet(i).Cells.Copy
Sheets.Add(After:=Sheets(IsheetCount)).Name = Ssheet(i).Name & i
ActiveSheet.Paste
Application.CutCopyMode = False
Next i
End Sub

Dave

OzGrid Business Applications