title: VBA - PivotTable Row Count and Copy Value to New Sheet
body: <p><strong>UPDATED</strong></p>
  
  <p>I need to do a row count of the rows in the pivot table between the <em>"header"</em> row of the table and the <em>"grand total"</em> row of the table and then copy the value that the count finds, to a summary sheet.</p>
  
  <p><strong>Header Row:</strong></p>
  
  <p><a href="https://i.stack.imgur.com/DP4JB.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DP4JB.png" alt="FirstRow"></a></p>
  
  <p><strong>Last Row:</strong></p>
  
  <p><a href="https://i.stack.imgur.com/vOlM0.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vOlM0.png" alt="LastRow"></a></p>
  
  <p>So I pretty much need to do a count from <strong>row 4 - row 133</strong> (which in this case is a total row count of 130) of the pivot table and paste that value in <strong>cell B23</strong> on the <strong>summary sheet</strong>.</p>
  
  <p>Current Pivot Table Layout:</p>
  
  <p><a href="https://i.stack.imgur.com/Gm9ef.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Gm9ef.png" alt="CurrentPivotTableLayout"></a></p>
  
  <p>I need to integrate the count into the code I already have, which is below:</p>
  
  <pre><code>Option Explicit
  
  Sub FilterPivotTable()
  
  Dim rLastCell As Range
  Dim PvtTbl As PivotTable
  Dim ws1 As Worksheet, ws2 As Worksheet
  Dim LastRow1 As Long
  Set ws1 = ActiveWorkbook.Sheets("PivotTable")
  Set ws2 = ActiveWorkbook.Sheets("Summary")
  
      LastRow1 = ws1.Cells(Rows.count, 1).End(xlUp).Row
  
      'Total - This inserts the word Total in cell B22 and pastes the total value 
  'found in the grand total line of the of the pivot table, in the summary sheet 
  'in line C22
  
      Application.ScreenUpdating = False
  
      ws1.PivotTables("P1").ManualUpdate = True
  
      ws1.PivotTables("P1").ManualUpdate = False
      Application.ScreenUpdating = True
  
      With ws1.PivotTables(1).TableRange1
          Set rLastCell = .Cells(.Rows.count, .Columns.count)
          Set PvtTbl = Worksheets("PivotTable").PivotTables("P1")
          rLastCell.Copy
  
          With ws2
  
          ws2.Cells(LastRow1 + 22, 3).PasteSpecial xlPasteValues
          .Range("$B$22").Value = "Total"
  
          End With
  
  
      End With
  
      'Microsoft Windows - This filters the table by any vulnerabilities that have 
  'the words Microsoft Windows in their description, then it inserts the words
  'Microsoft Windows in cell B2 and the grand total count of this filter
  'in cell C2
      Application.ScreenUpdating = False
  
      ws1.PivotTables("P1").ManualUpdate = True
  
      ws1.PivotTables("P1").PivotFields(" Vulnerability Name").ClearAllFilters
  
      ws1.PivotTables("P1").PivotFields(" Vulnerability Name").PivotFilters. _
      Add Type:=xlCaptionContains, Value1:="Microsoft Windows"
  
      ws1.PivotTables("P1").ManualUpdate = False
      Application.ScreenUpdating = True
  
      With ws1.PivotTables(1).TableRange1
          Set rLastCell = .Cells(.Rows.count, .Columns.count)
          Set PvtTbl = Worksheets("PivotTable").PivotTables("P1")
          rLastCell.Copy
  
          With ws2
  
              .Cells(LastRow1 + 2, 3).PasteSpecial xlPasteValues
              .Range("$B$2").Value = "Microsoft Windows"
  
          End With
  
      End With
  
      ws1.PivotTables("P1").PivotFields(" Vulnerability Name").ClearAllFilters
  
      'Microsoft Office- This filters the table by any vulnerabilities that have 
  'the words Microsoft Office in their description, then it inserts the words
  'Microsoft Windows in cell B3 and the grand total count of this filter
  'in cell C3
      Application.ScreenUpdating = False
      ws1.PivotTables("P1").ManualUpdate = True
  
      ws1.PivotTables("P1").PivotFields(" Vulnerability Name").PivotFilters. _
      Add Type:=xlCaptionContains, Value1:="Microsoft Office"
  
      ws1.PivotTables("P1").ManualUpdate = False
      Application.ScreenUpdating = True
  
      With ws1.PivotTables(1).TableRange1
          Set rLastCell = .Cells(.Rows.count, .Columns.count)
          Set PvtTbl = Worksheets("PivotTable").PivotTables("P1")
          rLastCell.Copy
  
          With ws2
  
          ws2.Cells(LastRow1 + 3, 3).PasteSpecial xlPasteValues
          .Range("$B$3").Value = "Microsoft Office"
  
          End With
  
  
      End With
  
      ws1.PivotTables("P1").PivotFields(" Vulnerability Name").ClearAllFilters
  
    End Sub
  </code></pre>
  
  <p>So far I have this code to do the count:</p>
  
  <pre><code>lRowCount = ActiveSheet.PivotTables("P1").TableRange1.Rows.Count
  </code></pre>
  
  <p>But not sure where to integrate this and how copy the lRowCount value.</p>
  
  <p>This row count needs to be done in each With block of code. So when the filter is done on vulnerability name for Microsoft Office, I need a row count done on the filtered data too.</p>