title: combining data from two sheets and generating pivot table in another sheet
body: <p>I have a sheet called Data and Missing. both the sheets contains the same number of columns. The Data sheet has data starting from row 5 and missing sheet has the data starting from row 2. </p>
  
  <p>I would like to generate a pivot table in my sheet "Dev", where i will have one pivot table that shows the data. </p>
  
  <p>I have provided an example image below for the same.</p>
  
  <p>I have the below code running for one sheet and produces pivot table. Could some one suggest, how I can do it with this requirement.</p>
  
  <pre><code>Sub pivotAPQP()
  Dim sp1 As Worksheet
  Dim pcache As PivotCache
  Dim ptable As PivotTable
  Dim ct As Integer
  Set sp1 = Sheets("Dev")
  'Se the pivot cache for pivot table
  Set pcache = ActiveWorkbook.PivotCaches.Create(xlDatabase, "'Data'!R4C1:R1048576C16")
  'set the pivot tbale in sheet
  Set ptable = pcache.CreatePivotTable(sp1.Range("A3"), TableName:="PivotTable1")
  'Decalre the parameter needed to be counted
  ptable.AddDataField ptable.PivotFields("COlour"), "Count of colour", xlCount
  'Declare the parameter for the row field adn arrange the values in descending order
  With ptable
  With .PivotFields("Loc")
  .Orientation = xlRowField
  .Position = 1
  .PivotItems("(blank)").Visible = False
  .AutoSort xlDescending, "Count of colour"
  End With
  'Declare the parameters for column field and alighn the values to center
  With .PivotFields("Colour")
  .Orientation = xlColumnField
  .Position = 1
  .PivotItems("(blank)").Visible = False
  ptable.TableRange2.Offset(0, 1).HorizontalAlignment = xlCenter
  End With
  End With
  End Sub
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/oA5Kg.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/oA5Kg.png" alt="this is my sheet1 structure. in original sheet i have 21 columns."></a></p>
  
  <p><a href="https://i.stack.imgur.com/PSa0U.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/PSa0U.png" alt="this is my sheet2, it is the same as sheet1, that i have 21 columns"></a></p>
  
  <p><a href="https://i.stack.imgur.com/XT4Zw.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XT4Zw.png" alt="this is my result sheet with pivot and would like to have a similar kind."></a></p>
  
  <p>EDIT: Trying to implement the code provided by the expert for my requirement</p>
  
  <pre><code>Sub pivotAPQP1()
  Dim wsData As Worksheet, wsMissing As Worksheet, wsPivot As Worksheet
  Dim tbl1 As ListObject, tbl2 As ListObject
  Dim pc As PivotCache, pt As PivotTable, pf As PivotField
  
  Application.ScreenUpdating = False
  
  Set wsData = Sheets("Data")
  Set wsMissing = Sheets("Missing")
  Set wsPivot = Sheets("Dev")
  wsPivot.Cells.Clear
  
  Set tbl1 = wsData.ListObjects("Table10")
  Set tbl2 = wsMissing.ListObjects("Table19")
  
  Set pc = ThisWorkbook.PivotCaches.Create( _
          SourceType:=xlConsolidation, _
          SourceData:=Array( _
          Array("'" &amp; wsData.Name &amp; "'!" &amp; tbl1.Range.Address(ReferenceStyle:=xlR1C1), wsData.Name), _
          Array("'" &amp; wsMissing.Name &amp; "'!" &amp; tbl2.Range.Address(ReferenceStyle:=xlR1C1), wsMissing.Name)))
  
  Set pt = pc.CreatePivotTable( _
              TableDestination:=wsPivot.Range("A3"), _
              TableName:="PivotTable1")
  pt.AddDataField pt.PivotFields("Colour"), "Count of colour", xlCount
  
  With pt
  With .PivotFields("Loc")
  .Orientation = xlRowField
  .Position = 1
  .PivotItems("(blank)").Visible = False
  .AutoSort xlDescending, "Count of colour"
  End With
  'Declare the parameters for column field and alighn the values to center
  With .PivotFields("Colour")
  .Orientation = xlColumnField
  .Position = 1
  .PivotItems("(blank)").Visible = False
  pt.TableRange2.Offset(0, 1).HorizontalAlignment = xlCenter
  End With
  End With
  End Sub
  </code></pre>