Problem of Inserting worksheet

[jwang036]

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

 

[Joel]

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

 

[Tom Ogilvy]

Why not make a workbook with 80 blank sheets, then just copy the cells from
the results sheet to each sheet as you run your tests. When pasting, use
pastespecial xlValues on each sheet.

 

[jwang036]

Can do, but I have two problems with this idea,

1. the number of tests may vary, so the number of sheets will vary too.
2. everytime doing the copy paste, I need to assign a tab name of a blank
sheet.

Both can be done by changing the script of course, but it won't be a small
change and I just want a small change if it's possible.