Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Excel 2000 - Copy Sheet

  Asked By: Lucas    Date: Jan 01    Category: MS Office    Views: 944
  

I'm trying to use a sheet to base other sheets on. When I have to
create a new sheet I'm using the following code.

Sub subCreateNewSheet(spType As String, _
spOldSchool As String)
' Copy base sheet and rename.

Dim slDSheet As String
Dim ilSheetNum As Integer
Dim ilNewSheetNum As Integer

Application.DisplayAlerts = False
DoEvents
slDSheet = UCase$(spType & " " & spOldSchool)

On Error Resume Next
Sheets(slDSheet).Delete
On Error GoTo 0

Sheets(spType & " School").Copy before:=Sheets(1)
Sheets(1).Name = slDSheet

Sheets(1).Name = slDSheet
Application.DisplayAlerts = True

End Sub

I'm getting an error 1004 at the copy line.

Anyone kave a clue why?

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Theresa Watson     Answered On: Jan 01

I tend to add sheets at the end using After rather than Before. Is it
possible that after the delete, there is no sheet(1)?

 
Answer #2    Answered By: Andrew Levensky     Answered On: Jan 01

Not in this situation... there are 23 sheets. I've inserted
For..Each code to go through the sheets and tell me numbers and
names.. that works.

I've also tried adding at the end and get the same result.

The strange thing is that after opening the workbook I can run the
main code 2 time with no problems but the 3rd time it always fails on
the copy. That subroutine I gave is actually run a number of times
during the "main" code... Also... after the code "stops working" then
I can't even do a wo/man-aul ( :-) ) copy  either! I go through the
motions and get no error but no extra sheet  either! Then I have to
close excel  down and open up again and everything works.

 
Answer #3    Answered By: Ella Oliver     Answered On: Jan 01

I played with your code and had no problems, but I did find

support.microsoft.com/default.aspx

Which explains there is a problem if you put it in a loop. The workaround is
to occasionally save the worksheet. (The article has an example)

 
Didn't find what you were looking for? Find more on Excel 2000 - Copy Sheet Or get search suggestion and latest updates.




Tagged:        

 
 
 
 

Related Post