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

Question

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

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

The links you have provided are not working. (Using Google Chrome) — Elias Wick
– Elias Wick, Commented Oct 11, 2017 at 10:28
@EliasWick i have uploaded again , could you let me know if you can access ? — Mikz
– Mikz, Commented Oct 11, 2017 at 11:44

QHarr · Accepted Answer · 2017-10-11 12:53:11Z

1

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 report

3) Select create a single page for me:

4) Add your table names as shown

5) Arrange fields as required

6) Uncheck the blank column that appears using dropdown

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.

edited Oct 11, 2017 at 12:53

answered Oct 11, 2017 at 12:13

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mikz Over a year ago

Thank you for your suggestion. That is really useful with screenshots.

Mikz Over a year ago

but i would like to have in code. Because i am trying to generate a report out of it.

Mikz Over a year ago

Could you help me how i can code with vba ? Because for deriving from one sheet, already i have a code , I am struck how i can consider two sheets and put them in one table

QHarr Over a year ago

Hi, you can record a macro and it provides you a code whilst doing this. You end up with key code lines being such as 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

QHarr Over a year ago

I have updated my answer with some more detail to help you.

|

Subodh Tiwari sktneer · Accepted Answer · 2017-10-11 14:11:12Z

1

Please give this a try...

Important things to know before you implement this code:

The code assumes that you have three sheets in the workbook called "Data", "Missing" and "Dev".
On Data Sheet, convert your data into an Excel Table and name it "Data".
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

answered Oct 11, 2017 at 14:11

Subodh Tiwari sktneer

9,9862 gold badges20 silver badges22 bronze badges

7 Comments

Mikz Over a year ago

it works ; but i am confused how i can mention the row field and column field ?

Mikz Over a year ago

because the row field i need for pivot is in column M of both the sheet. But in data sheet it starts with row 5 and in missing sheet it starts with row 2. and the column which i need for pivot is in Column L and i would like to have their count values

Mikz Over a year ago

could you tell me how i can ? I tried your code like below . you can see them in edit

Subodh Tiwari sktneer Over a year ago

It would be better to combine the data from the two sheets into a separate sheet and insert a normal pivot table based on that data set so that you can pick any field from the data set and set it's orientation.

Mikz Over a year ago

But I cant combine them, even though the headers are same, the content inside them are differrent. just the column L and M are same that i can put them under

|

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related