Excel 'Not Responding' issue when Copy Worksheet to newbook file

[FRANKinATLANTA]

We have a large Excel file with 12 worksheet tabs.

Each month, we need to copy 3 of the worksheets (WV, PA, VA) to a new file (Book1) to be emailed to a client. This is done by right-clicking the desired tab, selecting "Move or Copy", selecting the desired worksheet from the list, check the "Create a copy" box, and selecting (new book).

Two of the worksheets (WV, PA) always copy to the new file with no problems.

The third worksheet (VA) never copies to the new file. Excel goes into a "hung mode" and says "Not Responding". This happens even when we try to copy VA before the other two.

Obviously the settings for the overall file are not the problem since 2 of the worksheets copy as they should.

Is there a setting specific to the worksheet that is causing VA to act this way?

All help is very much appreciated! Thanks!!

[Tsjallie]

Does this third sheet happen to be a (very) large sheet?
Excel is not good in communicating it's busy. This message "not reponding" comes from windows.
I also encountered this message from time-to-time when I get impatient and click somewhere.
Most of the times it's just a matter of being patient and wait until Excel has done it's thing.
So it's not a setting.

[FRANKinATLANTA]

The Worksheets that are being copied are all identical in size, just different data. They are not large.

I now notice one distinct difference with the "problem worksheet" that may be the root of the problem. The "Scroll" slider bar on the far right edge of the worksheet does not let you drag the file down to the bottom. It's sort of an abbreviated scroll capability. To get down to say row 1200 you have to hold the bottom arrow on the scroll bar. This has to be related to the issue.

Is there a setting that changes how the worksheet scrolls down?

Thanks!

[Tsjallie]

The only setting related to the scrollbars that I know of is about the visibility of the scrollbars.
The size of the scrollbar is related to the size of the area containing data (Excel call this the used range).
So dragging the scrolbar down will take to the bottom of the area containing data.
Just put some value in a cell way below the last row containing data and you will see the scrollbar shrink. And so the scrollarea is increased.

If you then delete that value again, you won't see the scrollbar grow again.
That is because Excel tends to be a bit sloppy with keeping track of the size of the area containing data.

A possible cause of this problem sheet taking so long to copy may be that the usedrange for Excel is much larger than it in fact is.
In that case Excel "thinks" the sheet is (much) bigger than it really is.
You can test this by going to the problem sheet and [ctrl]+[end]. This take you to the lower right cell of the usedrange.
If that cell is way below the last row and/or the column containing any data then the usedrange is not set propery.
You can easily correct that by forcing Excel to reset the usedrange:
- Activate the problem sheet
- Bring up the VBA-editor by pressing [Alt]+[F11]
- In the VBA-editor window bring up the Immediate Window by pressing [Ctrl]+[G]
- In the Immediate Window type: "activesheet.usedrange" (without the quotes) and press [Enter]
This will force Excel to reset the usedrange for that sheet.
Pressing [Ctrl]+[End] in the sheet now will take you to the lower right cell of the real area containing any data.

Anyhow, there must be something in that sheet causing the problem.