Problem of Inserting worksheet
Below is how I like to write this code. I use variables to define each sheet
so it is easier to debug and maintain. I put the new sheet as the last sheet
in the code. You can change the following
from
.Sheets("Result").Copy _
After:=.Sheets(.Sheets.Count)
to
.Sheets("Result").Copy _
After:=.Sheets("Sheets.Count")
I suspect your problem may be with renaming the shhet to Testcode. doing
this all the sheet would have the same name. I add rowNumber to the sheet
name
For RowNum = 1 To 80
With Workbooks("LTP.XLS")
.Sheets("Result").Copy _
After:=.Sheets(.Sheets.Count)
Set newsht = ActiveSheet
newsht.Sheets("Result").Cells.Copy
newsht.PasteSpecial _
Paste:=xlPasteValues, _
Paste:=xlPasteFormats
newsht.Name = TestCode & RowNum
End With
Next RowNum
"jwang036" wrote:
Hi, I have a model generating some result (the final sheet is called
"Result") and I need to do 80 different tests. After each test I take out
the "result' sheet and put it into a summary file (LTP) and name the sheet
name as the test code. I use the following script. The LTP file has a start
sheet (sheet1). I copy that sheet1 and copy paste the result onto it then do
the next one. It works fine then stops after about 20 sheets. I can't even
manaully copy one more sheet (you know the drag-and-copy an existing sheet).
Any idea why? and how can I keep inserting work sheets. Thanks.
'Start the loop,from the 1st store of inputs
For RowNum = 1 To 80
Windows("LTP").Activate
Sheets("Sheet1").Copy After:=Sheets(1)
......Skip the generating result put......
'take out result sheet ,copy & paste value and change "Result" to test
code
Sheets("Result").Select
Cells.Select
Selection.Copy
Windows("LTP.xls").Activate
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Sheets("Sheet1 (2)").Name = TestCode
Next RowNum
|