Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro skips a command
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").Piv otCache.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 -- cm |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro skips a command
Try...
Worksheets("Summaries").Range("widthselection").Co lumnWidth = 18 -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "cm" wrote: 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").Piv otCache.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 -- cm |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro skips a command
Perfect; I am new to VBA -- why did the first way get ignored?
-- cm "Gary Brown" wrote: Try... Worksheets("Summaries").Range("widthselection").Co lumnWidth = 18 -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "cm" wrote: 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").Piv otCache.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 -- cm |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro skips a command
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. -- Rick (MVP - Excel) "cm" wrote in message ... Perfect; I am new to VBA -- why did the first way get ignored? -- cm "Gary Brown" wrote: Try... Worksheets("Summaries").Range("widthselection").Co lumnWidth = 18 -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "cm" wrote: 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").Piv otCache.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 -- cm |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro skips a command
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 "cm" wrote: Perfect; I am new to VBA -- why did the first way get ignored? -- cm "Gary Brown" wrote: Try... Worksheets("Summaries").Range("widthselection").Co lumnWidth = 18 -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "cm" wrote: 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").Piv otCache.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 -- cm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop Skips Data - Why? | Excel Programming | |||
skips controls | Excel Programming | |||
Macro deletes row in range, macro then skips the row moved up | Excel Discussion (Misc queries) | |||
How do I set it so my tab key skips to the next cell not 6? | Excel Discussion (Misc queries) | |||
Amortization with up front skips | Excel Worksheet Functions |