macro skips a command

[cm]

I have a button that runs the following codle. All of it works as intended
excpet the two lines that attempt to set the column widths of range named
'widthselection' to 18, after the pivot table has been refreshed.

Would like some advice, please.

Application.DisplayAlerts = False
With Sheets("Summaries")
.Unprotect Password:="topSecret"
Sheets("Summaries").PivotTables("PivotTable2").PivotCache.Refresh
Dim pt As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next pt
Next ws
For Each pc In ActiveWorkbook.PivotCaches
On Error Resume Next
pc.Refresh
Next pc
Range("widthselection").Select
Selection.ColumnWidth = 18
.Protect Password:="topSecret"
End With

 

[Gary Brown]

Try...
Worksheets("Summaries").Range("widthselection").ColumnWidth = 18

 

[cm]

Perfect; I am new to VBA -- why did the first way get ignored?

 

[Rick Rothstein]

I didn't try to run your code, but I'll take a guess at what may have gone
wrong in your code. This line...

Range("widthselection").Select

is inside a With/End With block, but because there is not a "dot" in front
of the Range keyword, it does not necessarily point to the object of the
With statement, namely, Sheets("Summaries"); rather, without the leading
"dot", it defaults to referring to the active sheet... if the active sheet
is not the "Summaries" sheet when you run the code, this line will not
function as you intended.

A couple of points on your coding. First, consider moving all your Dim
statements to the top of the procedure that they are in. There is no
positional relevance to their location as VB will search them all out as the
first order of business (no matter where they are located) so most people
find locating them at the beginning makes them easier to find when you need
to refer back to them. Second, has to do with your
Object.Select/Selection.<Action> structure. Perhaps this previous posting of
mine (a response to another person using Select/Selection type
constructions) will be of some help to you in your future programming...

Whenever you see code constructed like this...

Range("A1").Select
Selection.<whatever>

you can almost always do this instead...

Range("A1").<whatever>

In your particular case, you have this...

Range("C2:C8193").Select 'select cells to export
For Each r In Selection.Rows

which, using the above concept, can be reduced to this...

For Each r In Range("C2:C8193").Rows

Notice, all I have done is replace Selection with the range you Select(ed)
in the previous statement and eliminate the process of doing any
Select(ion)s. Stated another way, the Selection produced from
Range(...).Select is a range and, of course, Range(...) is a range... and,
in fact, they are the same range, so it doesn't matter which one you use.
The added benefit of not selecting ranges first is your active cell does not
change.

 

[Gary Brown]

I think it was a matter of 'scope'.
I can't tell from the code you've given, but I'll bet the active worksheet
that you call the macro from is NOT the 'Summaries' worksheet. Since the
range name 'widthselection' is associated with the 'Summaries' worksheet, the
macro couldn't find the 'widthselection' range until we added the
'Worksheets("Summaries").' qualified in front of the
'Range("widthselection").ColumnWidth' statement.

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown