Excel Moving sheet between workbooks takes forever on some workstations

  • Thread starter John M. Mach, MOUS Master, MCSC
  • Start date
J

John M. Mach, MOUS Master, MCSC

We have two workbooks. One we call Current and one we call Archive.
Once a week we want to move sheets from the Current workbook to the Archive
workbook.
When we do that, it can take 15-20 minutes to do the procedure, whether we
have 3 sheets or 30 sheets to move.
We have identified two workstations where this happens. We have another
workstation where we can do the same procedure and it only takes 1-2 minutes
max.
I am stumped as to why the same file behaves so differently on different
workstation. All have the same version of Office.
Any ideas? Has anyone else experienced this? How did you solve it?
Any help is appreciated.

TTFN
JMMach
 
H

Harlan Grove

John M. Mach, MOUS Master, MCSC wrote...
We have two workbooks. One we call Current and one we call Archive.
Once a week we want to move sheets from the Current workbook to the Archive
workbook.
When we do that, it can take 15-20 minutes to do the procedure, whether we
have 3 sheets or 30 sheets to move.
Click to expand...
....

Are you really *MOVING* the worksheet between workbooks? If so,
consider copying them to the archive workbook then deleting them in the
current workbook.

What sort of formulas are there in these worksheets? Do they refer to
ranges in other worksheets, both worksheets also being copied and not?
 
J

JMMach

I am moving the worksheets (select sheets, right-click on the sheet tab,
select Move or Copy (copy turned off), select destination workbook and
insert position, click OK)
Formulas have been replaced with their corresponding values using copy and
paste special, values.
What are you thinking the cause might be?
Thanks
TTFN
JMMach
 
H

Harlan Grove

JMMach said:
I am moving the worksheets (select sheets, right-click on the sheet tab,
select Move or Copy (copy turned off), select destination workbook and
insert position, click OK)
Formulas have been replaced with their corresponding values using copy and
paste special, values.
What are you thinking the cause might be?
Click to expand...
....

So the worksheets you're moving are only values. Are the worksheets
remaining only values, or do they contain formulas? If they contain
formulas, do any of those formulas contain references into the worksheets
you're moving? Are there any defined names?

Moving worksheets as opposed to copying them involves much more work on
Excel's part because it needs to maintain formula references into and out of
the moved worksheets. Copying worksheets only involves duplicating
references out of the copied worksheets. Just see if copying them then
deleting the originals takes any less time.
 
J

JMMach

I have not yet acted on your suggestion to copy and then delete, to see what
difference that makes.
But the real question is: Why is one box (Workstation) able to do it in less
than 2 minutes, and another box (identical hardware, Win2K SP4 and
Office2000) takes 15-20 minutes to do the same operation?
TTFN
JMMach
 
H

Harlan Grove

JMMach wrote...
I have not yet acted on your suggestion to copy and then delete, to see what
difference that makes.
But the real question is: Why is one box (Workstation) able to do it in less
than 2 minutes, and another box (identical hardware, Win2K SP4 and
Office2000) takes 15-20 minutes to do the same operation?
Click to expand...
....

The only two purely hardware reason I can think of that two supposedly
identical PCs would take greatly differring time for the same task
would be flaky RAM in one machine that Windows has detected but not
reported that might lead to using virtual memory or a faulty cache on
the slower machine.

Far more likely is that the two machines aren't actually identical in
what software, especially background services, they're running in
addition to running Excel. Overly intrusive antivirus software or so
many background services running that Excel needs to page virtual
memory would be suspects on the slower machines. Spyware/adware could
also be a culprit.
 
Top