title: How to suppress "overwrite existing data" alert within a pivottable update event?
body: <p><strong>Situation:</strong></p>
  
  <p>I am using a <code>PivotTableUpdate</code> event to clear a range to the right of a <code>PivotTable</code> and then insert values, in the next available column, to the right of the <code>PivotTable.DataBodyRange</code>.</p>
  
  <p><strong>Single slicer item selection:</strong></p>
  
  <p>In the first image below, I am selecting one slicer item and then using</p>
  
  <pre><code>.Offset(, .Columns.Count).Resize(.Rows.Count, 25)
  </code></pre>
  
  <p>to clear a region to the right of the <code>DataBodyRange</code>. The <code>Resize</code> is to account for prior selections which may have populated a different column from the currently next available.</p>
  
  <p>I am then using</p>
  
  <pre><code>.Offset(, .Columns.Count).Resize(.Rows.Count, 1) = "1"
  </code></pre>
  
  <p>to set the next available free column's value to, in this case, <code>1</code>.</p>
  
  <p><strong>Example: One slicer item selection, table and pivot  in Sheet1:</strong></p>
  
  <p><img src="https://i.stack.imgur.com/O8vQQ.png" width="500"></p>
  
  <p>So I am going from "before" to "after" as shown below:</p>
  
  <p><a href="https://i.stack.imgur.com/GxUCM.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GxUCM.png" alt="Before and after"></a></p>
  
  <p>This works fine. There is no change in the next available column address.</p>
  
  <p><strong>Multi-item slicer selection:</strong></p>
  
  <p>When I attempt the same thing with a multi-select the code halts with the standard "Alert before overwriting cells" i.e. I get:</p>
  
  <blockquote>
    <p>There is already data in..Do you want to replace it?</p>
  </blockquote>
  
  <p><strong>Example: Multi slicer item selection, table and pivot in Sheet1:</strong></p>
  
  <p><img src="https://i.stack.imgur.com/FoQyZ.png" width="500"></p>
  
  <p>What I have tried:</p>
  
  <pre><code>Application.DisplayAlerts = False
  </code></pre>
  
  <p>And</p>
  
  <pre><code>Application.AlertBeforeOverwriting = False
  </code></pre>
  
  <p>And (to clear the pop-up)</p>
  
  <pre><code>Application.SendKeys "{ENTER}"
  </code></pre>
  
  <p>The message still appears for multi-select and doesn't go away until I manually press the <kbd>OK</kbd></p>
  
  <p><strong>Thoughts:</strong></p>
  
  <p>I had thought perhaps the message is triggered before the event code is run, but I don't get this message when I overwrite the same range (i.e. single slicer item selection)</p>
  
  <p>I know you can manually switch this alert off via the File > Options > Advanced Settings but am unsure how to suppress, via code, in my current code.</p>
  
  <p><strong>Anyone have a solution on how to suppress this alert?</strong></p>
  
  <p><strong>Code:</strong></p>
  
  <pre><code>Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
  
      Application.DisplayAlerts = False
  
      With Target.DataBodyRange
  
          Dim clearRange As Range
  
          Set clearRange = .Offset(, .Columns.Count).Resize(.Rows.Count, 25)
  
          clearRange.ClearContents
  
          .Offset(, .Columns.Count).Resize(.Rows.Count, 1) = "1"
  
      End With
  
      Application.DisplayAlerts = True
  
  End Sub
  </code></pre>
  
  <p>Test data:</p>
  
  <pre><code>| Trust | STP  | Val |
  |-------|------|-----|
  | A     | STPa | 1   |
  | B     | STPb | 2   |
  | C     | STPc | 3   |
  </code></pre>
  
  <p>References:</p>
  
  <ol>
  <li><a href="https://www.ozgrid.com/forum/forum/help-forums/excel-general/84268-suppress-overwrite-existing-data-alert-in-vba-macro" rel="nofollow noreferrer">Suppress Overwrite Existing Data Alert In VBA Macro</a></li>
  <li><a href="https://stackoverflow.com/questions/25739577/prevent-overwrite-warning-in-vba">Prevent Overwrite Warning in VBA</a></li>
  </ol>