Smoothing transition using VBA with selecting sheets

[robinterrell]

I have created a macro that goes from my main sheet to the data sheet and changes all the pivot tables to match. One thing I hate about it is that it is not very fluid. It jumps from the first page to the second (due to the sheets("data") function. Is there a way to make it change the pivot tables on the second sheet without actually have to have the macro select the second sheet?

Thanks in advance.

Please Login or Register  to view this content.

[Marzuk]

Just add:

Application.ScreenUpdating = False

<code here>

Application.ScreenUpdating = True

This will prevent it. Also you do not have to use ActiveSheet etc, you can directly reference the sheet which avoids the need to even switch which sheet is active. Instead of:

Please Login or Register  to view this content.

use

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

(Or, if you have several to change...)

Please Login or Register  to view this content.

Additionally this is MUCH faster. If you are using code that "touches" a lot of cells it will be quite slow. This type of code is generated by the Macro Recorder, and usually needs to be edited after it is recorded to avoid this type of problem.

[JBeaucaire]

Please Login or Register  to view this content.

You don't fill in individual cells one a time with the same value. You do it all at once:

Please Login or Register  to view this content.

[Marzuk]

It was simply an example and I used 1 for illustration. It could just as easily have been "cat" and "dog".

[robinterrell]

Thank you both!!!!! This worked GREAT!!!