R
Richard Buttrey
I posted a question back in March, (thread "Loop time seems dependent
on unrelated workbook - Why?"), and have finally managed to identify
the problem area but still don't know what is causing it.
Hence this is more a general request for suggestions as to the cause,
since I have now put in a work around.
The problem was this. I have three workbooks.
In Book1 (which contains about 600 range names), a database sheet,
(which contains no formulae or names) is fed from another working
'paper' sheet. This database sheet is first output as an independent
file to create a second workbook.
Book 2 is used as a data import feed to a database sheet in a third
workbook. (Book2 is also used by other applications which is why I
don't feed directly from book1 to book3)
Book 3 contains amongst others, a database sheet, (fed by a copy and
paste from Book2), which is used by various other report template
sheets.
The 3 Workbooks. contain no links or range names in common.
The third reporting workbook has various macros which filter and
summarise data, hide rows, export data files and various other stuff,
based on a user choosing from various criteria.
This reporting workbook had been working fine for 18 months or so, but
last month it started a go slow campaign. A macro which typically
took about 30-45 seconds to process started taking many minutes.
I finally highlighted the problem data.
The first reporting Wb contains about 600 range names. The working
paper sheet and the database sheet (which is filtered from the working
paper sheet), each have circa 2000 rows of data. By iteratively
deleting half the range names in Book1, exporting the data to the 2nd
Wb. importing to the 3rd Wb, running the macro in the third Wb,
noting the time the macro took, and then either deleting half the
remaining names in Book1, or re-opening Book1 and deleting the other
half of the names, I eventually found one particular range name which
was causing the slow running. When I deleted just this one name from
Book1, everything was back to normal. (Deleting it and then
re-creating it did not solve the problem).
I am totally perplexed why this one name in Book1 should cause a
problem in what is essentially an independent, (no links or common
names) Book3.
The only other thing I should add is that the problem only exists when
Book1 is open in memory at the same time as Book3.
I'd be grateful for any ideas or suggestions as to what may be going
on here.
Usual TIA.
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
on unrelated workbook - Why?"), and have finally managed to identify
the problem area but still don't know what is causing it.
Hence this is more a general request for suggestions as to the cause,
since I have now put in a work around.
The problem was this. I have three workbooks.
In Book1 (which contains about 600 range names), a database sheet,
(which contains no formulae or names) is fed from another working
'paper' sheet. This database sheet is first output as an independent
file to create a second workbook.
Book 2 is used as a data import feed to a database sheet in a third
workbook. (Book2 is also used by other applications which is why I
don't feed directly from book1 to book3)
Book 3 contains amongst others, a database sheet, (fed by a copy and
paste from Book2), which is used by various other report template
sheets.
The 3 Workbooks. contain no links or range names in common.
The third reporting workbook has various macros which filter and
summarise data, hide rows, export data files and various other stuff,
based on a user choosing from various criteria.
This reporting workbook had been working fine for 18 months or so, but
last month it started a go slow campaign. A macro which typically
took about 30-45 seconds to process started taking many minutes.
I finally highlighted the problem data.
The first reporting Wb contains about 600 range names. The working
paper sheet and the database sheet (which is filtered from the working
paper sheet), each have circa 2000 rows of data. By iteratively
deleting half the range names in Book1, exporting the data to the 2nd
Wb. importing to the 3rd Wb, running the macro in the third Wb,
noting the time the macro took, and then either deleting half the
remaining names in Book1, or re-opening Book1 and deleting the other
half of the names, I eventually found one particular range name which
was causing the slow running. When I deleted just this one name from
Book1, everything was back to normal. (Deleting it and then
re-creating it did not solve the problem).
I am totally perplexed why this one name in Book1 should cause a
problem in what is essentially an independent, (no links or common
names) Book3.
The only other thing I should add is that the problem only exists when
Book1 is open in memory at the same time as Book3.
I'd be grateful for any ideas or suggestions as to what may be going
on here.
Usual TIA.
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________