Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming sheets - Performance problem
I'm looking for help regarding a performance problem I have with a VB macro
that renames sheets in an excel workbook. I have a macro that renames sheets in a workbook. The workbook contains approx. 52 sheets that need to be renamed/reordered. On a first pass the sheets are renamed to a new temporary name which takes about 0.2 seconds per sheet (±10 seconds for 50 sheets). On the second pass the sheets are renamed to the final new name which takes about 7 seconds per sheet (±350 seconds for 50 sheets). After this renaming action sheets are reordered i.e. placed in ascending order which takes about 0.1 second per sheet. Each sheet contains data, graphs, named areas and have refrences to each other. During the renaming the Calculation is off. The names of the sheets are numbers from 1 to 52. On the first pass they are renamed into R1 to R52, on the second pass back again into 1 to 52 (sheets may get a new number depending on sort criteria i.e. what was sheet 1 may become sheet 5, etc.). |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming sheets - Performance problem
Hi
Try to turn off ScreenUpdating during the process. Regards, Per "JoHar" skrev i meddelelsen ... I'm looking for help regarding a performance problem I have with a VB macro that renames sheets in an excel workbook. I have a macro that renames sheets in a workbook. The workbook contains approx. 52 sheets that need to be renamed/reordered. On a first pass the sheets are renamed to a new temporary name which takes about 0.2 seconds per sheet (±10 seconds for 50 sheets). On the second pass the sheets are renamed to the final new name which takes about 7 seconds per sheet (±350 seconds for 50 sheets). After this renaming action sheets are reordered i.e. placed in ascending order which takes about 0.1 second per sheet. Each sheet contains data, graphs, named areas and have refrences to each other. During the renaming the Calculation is off. The names of the sheets are numbers from 1 to 52. On the first pass they are renamed into R1 to R52, on the second pass back again into 1 to 52 (sheets may get a new number depending on sort criteria i.e. what was sheet 1 may become sheet 5, etc.). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming sheets - Performance problem
And try turning calculation to manual
then do the work then turning calculation back to what it was when you started. JoHar wrote: I'm looking for help regarding a performance problem I have with a VB macro that renames sheets in an excel workbook. I have a macro that renames sheets in a workbook. The workbook contains approx. 52 sheets that need to be renamed/reordered. On a first pass the sheets are renamed to a new temporary name which takes about 0.2 seconds per sheet (±10 seconds for 50 sheets). On the second pass the sheets are renamed to the final new name which takes about 7 seconds per sheet (±350 seconds for 50 sheets). After this renaming action sheets are reordered i.e. placed in ascending order which takes about 0.1 second per sheet. Each sheet contains data, graphs, named areas and have refrences to each other. During the renaming the Calculation is off. The names of the sheets are numbers from 1 to 52. On the first pass they are renamed into R1 to R52, on the second pass back again into 1 to 52 (sheets may get a new number depending on sort criteria i.e. what was sheet 1 may become sheet 5, etc.). -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming sheets - Performance problem
Calculation and screen updating are both off....
"Dave Peterson" wrote: And try turning calculation to manual then do the work then turning calculation back to what it was when you started. JoHar wrote: I'm looking for help regarding a performance problem I have with a VB macro that renames sheets in an excel workbook. I have a macro that renames sheets in a workbook. The workbook contains approx. 52 sheets that need to be renamed/reordered. On a first pass the sheets are renamed to a new temporary name which takes about 0.2 seconds per sheet (±10 seconds for 50 sheets). On the second pass the sheets are renamed to the final new name which takes about 7 seconds per sheet (±350 seconds for 50 sheets). After this renaming action sheets are reordered i.e. placed in ascending order which takes about 0.1 second per sheet. Each sheet contains data, graphs, named areas and have refrences to each other. During the renaming the Calculation is off. The names of the sheets are numbers from 1 to 52. On the first pass they are renamed into R1 to R52, on the second pass back again into 1 to 52 (sheets may get a new number depending on sort criteria i.e. what was sheet 1 may become sheet 5, etc.). -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming sheets - Performance problem
And it works fine?????
If no, then I don't have another suggestion. JoHar wrote: Calculation and screen updating are both off.... "Dave Peterson" wrote: And try turning calculation to manual then do the work then turning calculation back to what it was when you started. JoHar wrote: I'm looking for help regarding a performance problem I have with a VB macro that renames sheets in an excel workbook. I have a macro that renames sheets in a workbook. The workbook contains approx. 52 sheets that need to be renamed/reordered. On a first pass the sheets are renamed to a new temporary name which takes about 0.2 seconds per sheet (±10 seconds for 50 sheets). On the second pass the sheets are renamed to the final new name which takes about 7 seconds per sheet (±350 seconds for 50 sheets). After this renaming action sheets are reordered i.e. placed in ascending order which takes about 0.1 second per sheet. Each sheet contains data, graphs, named areas and have refrences to each other. During the renaming the Calculation is off. The names of the sheets are numbers from 1 to 52. On the first pass they are renamed into R1 to R52, on the second pass back again into 1 to 52 (sheets may get a new number depending on sort criteria i.e. what was sheet 1 may become sheet 5, etc.). -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming sheets - Performance problem
What I mean is that during the renaming operation I described the calculation
and screen updating is off. reading your answer this means there is no solution.. :-( "Dave Peterson" wrote: And it works fine????? If no, then I don't have another suggestion. JoHar wrote: Calculation and screen updating are both off.... "Dave Peterson" wrote: And try turning calculation to manual then do the work then turning calculation back to what it was when you started. JoHar wrote: I'm looking for help regarding a performance problem I have with a VB macro that renames sheets in an excel workbook. I have a macro that renames sheets in a workbook. The workbook contains approx. 52 sheets that need to be renamed/reordered. On a first pass the sheets are renamed to a new temporary name which takes about 0.2 seconds per sheet (ÀšÃ‚±10 seconds for 50 sheets). On the second pass the sheets are renamed to the final new name which takes about 7 seconds per sheet (ÀšÃ‚±350 seconds for 50 sheets). After this renaming action sheets are reordered i.e. placed in ascending order which takes about 0.1 second per sheet. Each sheet contains data, graphs, named areas and have refrences to each other. During the renaming the Calculation is off. The names of the sheets are numbers from 1 to 52. On the first pass they are renamed into R1 to R52, on the second pass back again into 1 to 52 (sheets may get a new number depending on sort criteria i.e. what was sheet 1 may become sheet 5, etc.). -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming sheets - Performance problem
I only mean that I don't have another suggestion.
I don't know if there's a solution or not. JoHar wrote: What I mean is that during the renaming operation I described the calculation and screen updating is off. reading your answer this means there is no solution.. :-( "Dave Peterson" wrote: And it works fine????? If no, then I don't have another suggestion. JoHar wrote: Calculation and screen updating are both off.... "Dave Peterson" wrote: And try turning calculation to manual then do the work then turning calculation back to what it was when you started. JoHar wrote: I'm looking for help regarding a performance problem I have with a VB macro that renames sheets in an excel workbook. I have a macro that renames sheets in a workbook. The workbook contains approx. 52 sheets that need to be renamed/reordered. On a first pass the sheets are renamed to a new temporary name which takes about 0.2 seconds per sheet (ÀšÃ‚±10 seconds for 50 sheets). On the second pass the sheets are renamed to the final new name which takes about 7 seconds per sheet (ÀšÃ‚±350 seconds for 50 sheets). After this renaming action sheets are reordered i.e. placed in ascending order which takes about 0.1 second per sheet. Each sheet contains data, graphs, named areas and have refrences to each other. During the renaming the Calculation is off. The names of the sheets are numbers from 1 to 52. On the first pass they are renamed into R1 to R52, on the second pass back again into 1 to 52 (sheets may get a new number depending on sort criteria i.e. what was sheet 1 may become sheet 5, etc.). -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming sheets - Performance problem
On Oct 3, 3:05*am, "Per Jessen" wrote:
Hi Try to turn off ScreenUpdating during the process. Regards, Per "JoHar" skrev i ... I'm looking for help regarding a performance problem I have with a VB macro that renamessheetsin an excel workbook. I have a macro that renamessheetsin a workbook. The workbook contains approx. 52sheetsthat need to be renamed/reordered. On a first pass the sheetsare renamed to a new temporary name which takes about 0.2 seconds per sheet (±10 seconds for 50sheets). On the second pass thesheetsare renamed to the final new name which takes about 7 seconds per sheet (±350 seconds for 50sheets). After this renaming actionsheetsare reordered i.e. placed in ascending order which takes about 0.1 second per sheet. Each sheet contains data, graphs, named areas and have refrences to each other. During the renaming the Calculation is off. The names of thesheetsare numbers from 1 to 52. On the first pass they are renamed into R1 to R52, on the second pass back again into 1 to 52 (sheetsmay get a new number depending on sort criteria i.e. what was sheet 1 may become sheet 5, etc.). At HIGH-THREAD-COUNT-BED-SHEETS.COM, we have chosen to work with only the top luxury bed linen manufacturers in the world. We utilize our industry experience and contacts to bring you the finest high thread count bed sheets we can find. http://www.high-thread-count-bed-she...uselinens.html |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming sheets - Performance problem
On Oct 3, 2:36*am, JoHar wrote:
I'm looking for help regarding a performance problem I have with a VB macro that renamessheetsin an excel workbook. I have a macro that renamessheetsin a workbook. The workbook contains approx. 52sheetsthat need to be renamed/reordered. On a first pass thesheetsare renamed to a new temporary name which takes about 0.2 seconds per sheet (±10 seconds for 50sheets). On the second pass thesheetsare renamed to the final new name which takes about 7 seconds per sheet (±350 seconds for 50sheets). After this renaming actionsheetsare reordered i.e. placed in ascending order which takes about 0.1 second per sheet. Each sheet contains data, graphs, named areas and have refrences to each other. During the renaming the Calculation is off. The names of thesheetsare numbers from 1 to 52. On the first pass they are renamed into R1 to R52, on the second pass back again into 1 to 52 (sheetsmay get a new number depending on sort criteria i.e. what was sheet 1 may become sheet 5, etc.). At HIGH-THREAD-COUNT-BED-SHEETS.COM, we have chosen to work with only the top luxury bed linen manufacturers in the world. We utilize our industry experience and contacts to bring you the finest high thread count bed sheets we can find.http://www.high-thread-count-bed- sheets.com/organicbedding.html |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming sheets - Performance problem
On Oct 3, 3:05*am, "Per Jessen" wrote:
Hi Try to turn off ScreenUpdating during the process. Regards, Per "JoHar" skrev i ... I'm looking for help regarding a performance problem I have with a VB macro that renamessheetsin an excel workbook. I have a macro that renamessheetsin a workbook. The workbook contains approx. 52sheetsthat need to be renamed/reordered. On a first pass the sheetsare renamed to a new temporary name which takes about 0.2 seconds per sheet (±10 seconds for 50sheets). On the second pass thesheetsare renamed to the final new name which takes about 7 seconds per sheet (±350 seconds for 50sheets). After this renaming actionsheetsare reordered i.e. placed in ascending order which takes about 0.1 second per sheet. Each sheet contains data, graphs, named areas and have refrences to each other. During the renaming the Calculation is off. The names of thesheetsare numbers from 1 to 52. On the first pass they are renamed into R1 to R52, on the second pass back again into 1 to 52 (sheetsmay get a new number depending on sort criteria i.e. what was sheet 1 may become sheet 5, etc.). At HIGH-THREAD-COUNT-BED-SHEETS.COM, we have chosen to work with only the top luxury bed linen manufacturers in the world. We utilize our industry experience and contacts to bring you the finest high thread count bed sheets we can find.http://www.high-thread-count-bed- sheets.com/luba.html |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming sheets - Performance problem
On Oct 4, 5:01*am, JoHar wrote:
Calculation and screen updating are both off.... "Dave Peterson" wrote: And try turning calculation to manual then do the work then turning calculation back to what it was when you started. JoHar wrote: I'm looking for help regarding a performance problem I have with a VB macro that renamessheetsin an excel workbook. I have a macro that renamessheetsin a workbook. The workbook contains approx. 52sheetsthat need to be renamed/reordered. On a first pass the sheetsare renamed to a new temporary name which takes about 0.2 seconds per sheet (±10 seconds for 50sheets). On the second pass thesheetsare renamed to the final new name which takes about 7 seconds per sheet (±350 seconds for 50sheets). After this renaming actionsheetsare reordered i.e. placed in ascending order which takes about 0.1 second per sheet. Each sheet contains data, graphs, named areas and have refrences to each other. During the renaming the Calculation is off. The names of thesheetsare numbers from 1 to 52. On the first pass they are renamed into R1 to R52, on the second pass back again into 1 to 52 (sheetsmay get a new number depending on sort criteria i.e. what was sheet 1 may become sheet 5, etc.). -- Dave Peterson At HIGH-THREAD-COUNT-BED-SHEETS.COM, we have chosen to work with only the top luxury bed linen manufacturers in the world. We utilize our industry experience and contacts to bring you the finest high thread count bed sheets we can find. http://www.high-thread-count-bed-she...omforters.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Renaming Sheets | Excel Discussion (Misc queries) | |||
Renaming sheets | Excel Programming | |||
renaming sheets | Excel Programming | |||
Renaming sheets | Excel Programming | |||
renaming sheets | Excel Programming |