Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem of Inserting worksheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem of Inserting worksheet
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. -- Regards, Tom Ogilvy "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem of Inserting worksheet
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. "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with inserting picture. | Excel Programming | |||
problem in deleting and inserting a row | Excel Programming | |||
little problem with inserting a formula | Excel Programming | |||
Problem inserting a new worksheet | Excel Discussion (Misc queries) | |||
Sorting and inserting problem | Excel Programming |