combining data from two sheets and generating pivot table in another sheet

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.

I would like to generate a pivot table in my sheet "Dev", where i will have one pivot table that shows the data.

I have provided an example image below for the same.

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.

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

this is my sheet1 structure. in original sheet i have 21 columns.

this is my sheet2, it is the same as sheet1, that i have 21 columns

this is my result sheet with pivot and would like to have a similar kind.

EDIT: Trying to implement the code provided by the expert for my requirement

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("'" & wsData.Name & "'!" & tbl1.Range.Address(ReferenceStyle:=xlR1C1), wsData.Name), _
        Array("'" & wsMissing.Name & "'!" & 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

2 answers

  • answered 2017-10-11 12:13 QHarr

    You don't need VBA. If you set both the source ranges up as tables and note their names e.g. Table3 and Table4 then

    1) Set source ranges up as tables

    2) Press Alt, D, P to open the pivottable wizard and select Multiple consolidation ranges and create pivotable reportWizard

    3) Select create a single page for me:

    single page option

    4) Add your table names as shown

    Table name entry

    5) Arrange fields as required Field arrangement

    6) Uncheck the blank column that appears using dropdown Remove blank column

    You can give some of the items more meaningful names than shown here.

    With VBA you can record a macro whilst performing the above steps to get an idea of the code steps and syntax.

    An example, would be tailored to your environment looks like as follows:

       Sub CreatePivotMultiRange1()
    '
    ' CreatePivotMultiRange1 Macro
    '
    
    '
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
            Array(Array("Table3[#All]", "Item1"), Array("Table4[#All]", "Item2")), Version:= _
            6).CreatePivotTable TableDestination:="[Book1]Sheet7!R15C8", TableName:= _
            "PivotTable7", DefaultVersion:=6
        ActiveSheet.PivotTables("PivotTable7").DataPivotField.PivotItems( _
            "Count of Value").Position = 1
        With ActiveSheet.PivotTables("PivotTable7").PivotFields("Value")
            .Orientation = xlColumnField
            .Position = 2
        End With
        ActiveWorkbook.ShowPivotTableFieldList = False
        Range("L18").Select
        With ActiveSheet.PivotTables("PivotTable7").PivotFields("Row")
            .PivotItems("(blank)").Visible = False
        End With
        Range("K16").Select
        ActiveSheet.PivotTables("PivotTable7").PivotFields("Column").Subtotals = Array( _
            False, False, False, False, False, False, False, False, False, False, False, False)
        ActiveSheet.PivotTables("PivotTable7").PivotSelect "'Column Grand Total'", _
            xlDataAndLabel + xlFirstRow, True
        ActiveSheet.PivotTables("PivotTable7").ColumnGrand = False
    End Sub
    

    Your lines:

    Set pcache = ActiveWorkbook.PivotCaches.Create(xlDatabase, "'Data'!R4C1:R1048576C16")
    'set the pivot tbale in sheet
    Set ptable = pcache.CreatePivotTable(sp1.Range("A3"), TableName:="PivotTable1")
    

    Becomes something like:

     ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
        Array(Array("Table3[#All]", "Item1"), Array("Table4[#All]", "Item2")), Version:= _
        6).CreatePivotTable TableDestination:=sp1.Range("A3"), TableName:= _
        "PivotTable1", DefaultVersion:=6
    
    Set pt = sp1.PivotTables("PivotTable1")
    

    And you add the following line at the top with the declarations:

     Dim pt As PivotTable
    

    You will need to do some code updating potentially as you have done a ptable field addition.

    The rest of the code recorded by the macro gives you the layout.

  • answered 2017-10-11 14:11 sktneer

    Please give this a try...

    Important things to know before you implement this code:

    1. The code assumes that you have three sheets in the workbook called "Data", "Missing" and "Dev".
    2. On Data Sheet, convert your data into an Excel Table and name it "Data".
    3. Similarly, on Missing Sheet, convert your data into an Excel Table and name it "Missing".

    That's all you need to implement this code to your workbook. The code will create a Pivot Table on Dev Sheet in your desired format.

    Code:

    Sub CreatePivotTable()
    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("Data")
    Set tbl2 = wsMissing.ListObjects("Missing")
    
    Set pc = ThisWorkbook.PivotCaches.Create( _
            SourceType:=xlConsolidation, _
            SourceData:=Array( _
            Array("'" & wsData.Name & "'!" & tbl1.Range.Address(ReferenceStyle:=xlR1C1), wsData.Name), _
            Array("'" & wsMissing.Name & "'!" & tbl2.Range.Address(ReferenceStyle:=xlR1C1), wsMissing.Name)))
    
    Set pt = pc.CreatePivotTable( _
                TableDestination:=wsPivot.Range("A3"), _
                TableName:="PivotTable1")
    
    Set pf = pt.PivotFields("Value")
    pf.Orientation = xlColumnField
    Set pf = pt.PivotFields("Column")
    pf.Orientation = xlHidden
    pt.ColumnGrand = False
    Application.ScreenUpdating = True
    End Sub