For Each In ThisWorkbook.Worksheets failing to apply to all sheets

[PaulM100]

So, I'm using the following piece of code from Ron de Bruin to change my formulas into values in all sheets. I am using a Select as I have some pivots and it throws an error on .Value=.Value syntax.
The strangest thing is that is not applying to all sheets. Basically, it works on all of them except for last 6 out of 20.
There is no error at all, the code works, but doesn't change into values.

I hope someone has an idea as why, as I spent the last few hours to fix it without success.

Please Login or Register  to view this content.

[rorya]

I can't see anything wrong with that code. I don't follow your comment about Selecting because of pivot tables - how are the two related?

[PaulM100]

I don't see anything wrong either. If I am using the following code instead it throws an error on pivots. I was just trying to explain the choice of code

Please Login or Register  to view this content.

But the problem still occurs. The last 6 sheets are not changed to values, the formulas are in place.

[rorya]

You can still copy/paste without selecting.

Do you see all the sheets being selected in turn?

[PaulM100]

I think I found out the reason. So in these sheets I have some filtered out rows using a formula. I also have some columns/rows grouped. I don't think that the grouped ranges are causing problems, but maybe the filtered rows are. But there is no error. How comes?

[rorya]

Do you have any error handling in your code?

[PaulM100]

None. But I kinda solved the issue. I added an autofilter line and now it works smothly. Still don't understand why it didn't threw an error but at least I managed to fix it.

Please Login or Register  to view this content.