Renaming sheets - Performance problem


J

JoHar

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.).
 
Ad

Advertisements

D

Dave Peterson

And try turning calculation to manual
then do the work
then turning calculation back to what it was when you started.
 
J

JoHar

Calculation and screen updating are both off....

Dave Peterson said:
And try turning calculation to manual
then do the work
then turning calculation back to what it was when you started.
 
J

JoHar

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.. :-(
 
Ad

Advertisements

D

Dave Peterson

I only mean that I don't have another suggestion.

I don't know if there's a solution or not.
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.. :-(
 
S

SEO

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
 
S

SEO

Hi

Try to turn off ScreenUpdating during the process.

Regards,
Per

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
 
Ad

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top