Loop thru ALL sheets, resizing one column in each based on certain criteria

[jerrydiaz]

Hi -- I have the following code and it works great except for the fact that it only works on the active sheet and I'm not sure what needs to be tweaked. Any suggestions please? Thank you!

Please Login or Register  to view this content.

[mehmetcik]

The code looks ok, Looping wise so you probably have an error that causes VBA to crash before it can loop.

I am going to step through your code to see what happens, but you may need to do that on your spreadsheet.#

Ok the macro works as intended with one issue.

Nowhere do you ask the macro to switch is focus to a sheet other than the active sheet.

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[jerrydiaz]

Thanks, that would be great. I just realized I forgot to include Dim statements for ws and wb in my sample because I declare them globally in the module.

[jerrydiaz]

Ok, I added the "With" statement, but the "strLongestCellRef" variable still seems to be defined by the first worksheet rather than redefine itself on each sheet.

[Norie]

This will only refer to the active sheet.

Please Login or Register  to view this content.

Try adding the worksheet reference ws.

Please Login or Register  to view this content.

[AB33]

Please Login or Register  to view this content.

[jerrydiaz]

Thank you AB33! That did it!

Thank you Norie and mehmetcik for all your help too!

[jerrydiaz]

Hi Guys-- I just ran this on a couple of workbooks and it seems that I am still running into an issue. While the code successfully loops through each sheet, the strLongestCellRef and intLongestCellLen are still referencing the longest string in column L of the first worksheet rather than resetting itself after each loop. I don't understand why this is happening. lngLastRow is returning an accurate reference to each successive worksheet but not the other 2. I have tried:

Please Login or Register  to view this content.

and regardless of which, strLongestCellRef and intLongestCellLen refer to worksheet 1. What am I missing?
Thanks

[jerrydiaz]

Actually I just realized - it's not that it keeps referring to sheet 1. It continues reffering to the longest string it encounters. So If there is a 102-character string in sheet 2 and no other cell contains a string as long until sheet 6, sheet 2 will remain the reference.