Printable View
Hey All,
I have face this trouble when trying to copy sheet.
[VBA]
Sheets("SheetName").Move Before:=Sheets(1)[/VBA]
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
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?
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 ;)
[vba]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[/vba]
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.