Help:How to identify the sheet number of the first sheet?

[yurble_vn]

Hey All,

I have face this trouble when trying to copy sheet.

Sheets("SheetName").Move Before:=Sheets(1)

With the above code, I try to move one sheet to the first position.

But, unfuturnately, when I delete the first sheet, without close the file, and run this code again. And it return error.

With the same code, but after I save and close the file, and re-open it works again.

Please help

[JonPeltier]

In a simple test I could not repro this problem.

What error do you get? Did you delete the named sheet, then try to reference it again?

[yurble_vn]

I have used this code to copy sheet with dynamics chart.

Just test again. It works well. Dont know what wrong with old sheet.

Let i try it and post later.

thank for your prompt reply ;)

Sub CopySheetWithDynamicsChart()
Dim nmeOrgSht As String
Dim nmeDivSht As String
Dim nmeOrgBook As String
Dim nmeDivBook As String
Dim numCopy As Integer
Dim icount As Integer

numCopy = InputBox("Insert Number of Copy", "test", 3)

'get the current Workbook Name
nmeOrgBook = ActiveWorkbook.Name
'get the current WorkSheet Name
nmeOrgSht = ActiveSheet.Name
'Open new workbook
Workbooks.Add
'get newbook name
nmeDivBook = ActiveWorkbook.Name
'move the sheet intend to copy
Workbooks(nmeOrgBook).Sheets(nmeOrgSht).Move Before:=Workbooks(nmeDivBook).Sheets(1)
'get it name in new book
nmeDivSht = ActiveSheet.Name
'save and close new book as template file
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\temp.xls"
nmeDivBook = "temp"
ActiveWorkbook.Close False

'Copy loop
For icount = 1 To numCopy
Workbooks.Open Filename:="C:\temp.xls"
Sheets(nmeDivSht).Move Before:=Workbooks(nmeOrgBook).Sheets(1)
Workbooks(nmeDivBook).Close False
Next

Kill ("C:\temp.xls")
End Sub

[yurble_vn]

Have tested

After copy some sheets (22 sheets). Then delete copied sheets. Do it sometimes, then the errors occured:

Run-time error '1004':
You cannot save this workbook with the same name as another open workbook or add-in. Choose a different name, or close the other workbook or add-in before saving.