[VBA HELP] Copy Data.

S

sasils

New Member
Joined
Jun 3, 2017
Messages
24
Hi everyone,

I need help on VBA code to copy and merge data from 2 pivot tables into a single data set to be display in another sheet.

Below is data Sheet 1 in excel.

[TABLE="class: cms_table, width: 388"]
<tbody>[TR]
[TD="align: left"]Pivot A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Shipto[/TD]
[TD="align: left"]Soldto[/TD]
[TD="align: left"]LV1[/TD]
[TD="align: left"]LV2[/TD]
[TD="align: left"]LV3[/TD]
[/TR]
[TR]
[TD="align: left"]001[/TD]
[TD="align: left"]A[/TD]
[TD="align: left"]Local[/TD]
[TD="align: left"]NE[/TD]
[TD="align: left"]Manufacturer[/TD]
[/TR]
[TR]
[TD="align: left"]002[/TD]
[TD="align: left"]B[/TD]
[TD="align: left"]Local[/TD]
[TD="align: left"]NE[/TD]
[TD="align: left"]Manufacturer[/TD]
[/TR]
[TR]
[TD="align: left"]003[/TD]
[TD="align: left"]C[/TD]
[TD="align: left"]Global[/TD]
[TD="align: left"]CE[/TD]
[TD="align: left"]Trader[/TD]
[/TR]
[TR]
[TD="align: left"]004[/TD]
[TD="align: left"]D[/TD]
[TD="align: left"]Global[/TD]
[TD="align: left"]CE[/TD]
[TD="align: left"]Whole Sale[/TD]
[/TR]
[TR]
[TD="align: left"]005[/TD]
[TD="align: left"]E[/TD]
[TD="align: left"]Others[/TD]
[TD="align: left"]S[/TD]
[TD="align: left"]Mini Mart[/TD]
[/TR]
[TR]
[TD="align: left"]006[/TD]
[TD="align: left"]E[/TD]
[TD="align: left"]Others[/TD]
[TD="align: left"]S[/TD]
[TD="align: left"]Mini Mart[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="class: cms_table, width: 305"]
<tbody>[TR]
[TD="align: left"]Pivot B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Mat Code[/TD]
[TD="align: left"]Description[/TD]
[TD="align: left"]LV1[/TD]
[TD="align: left"]LV2[/TD]
[/TR]
[TR]
[TD="align: left"]S001[/TD]
[TD="align: left"]AAA[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: left"]S002[/TD]
[TD="align: left"]BBB[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: left"]S003[/TD]
[TD="align: left"]CCC[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: left"]S004[/TD]
[TD="align: left"]DDD[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: left"]S005[/TD]
[TD="align: left"]EEE[/TD]
[TD="align: left"]Paper[/TD]
[TD="align: left"]PM[/TD]
[/TR]
[TR]
[TD="align: left"]S006[/TD]
[TD="align: left"]FFF[/TD]
[TD="align: left"]Paper[/TD]
[TD="align: left"]PM[/TD]
[/TR]
</tbody>[/TABLE]


Final Data to use data from Pivot A and B into a data set in Sheet 2. The result should look like below.

[TABLE="class: cms_table, width: 665"]
<tbody>[TR]
[TD="align: left"]Final Table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Shipto[/TD]
[TD="align: left"]Soldto[/TD]
[TD="align: left"]LV1[/TD]
[TD="align: left"]LV2[/TD]
[TD="align: left"]LV3[/TD]
[TD="align: left"]Mat Code[/TD]
[TD="align: left"]Description[/TD]
[TD="align: left"]LV1[/TD]
[TD="align: left"]LV2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: left"]A[/TD]
[TD="align: left"]Local[/TD]
[TD="align: left"]NE[/TD]
[TD="align: left"]Manufacturer[/TD]
[TD="align: left"]S001[/TD]
[TD="align: left"]AAA[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: left"]A[/TD]
[TD="align: left"]Local[/TD]
[TD="align: left"]NE[/TD]
[TD="align: left"]Manufacturer[/TD]
[TD="align: left"]S002[/TD]
[TD="align: left"]BBB[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: left"]A[/TD]
[TD="align: left"]Local[/TD]
[TD="align: left"]NE[/TD]
[TD="align: left"]Manufacturer[/TD]
[TD="align: left"]S003[/TD]
[TD="align: left"]CCC[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: left"]A[/TD]
[TD="align: left"]Local[/TD]
[TD="align: left"]NE[/TD]
[TD="align: left"]Manufacturer[/TD]
[TD="align: left"]S004[/TD]
[TD="align: left"]DDD[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: left"]A[/TD]
[TD="align: left"]Local[/TD]
[TD="align: left"]NE[/TD]
[TD="align: left"]Manufacturer[/TD]
[TD="align: left"]S005[/TD]
[TD="align: left"]EEE[/TD]
[TD="align: left"]Paper[/TD]
[TD="align: left"]PM[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: left"]A[/TD]
[TD="align: left"]Local[/TD]
[TD="align: left"]NE[/TD]
[TD="align: left"]Manufacturer[/TD]
[TD="align: left"]S006[/TD]
[TD="align: left"]FFF[/TD]
[TD="align: left"]Paper[/TD]
[TD="align: left"]PM[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: left"]B[/TD]
[TD="align: left"]Local[/TD]
[TD="align: left"]NE[/TD]
[TD="align: left"]Manufacturer[/TD]
[TD="align: left"]S001[/TD]
[TD="align: left"]AAA[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: left"]B[/TD]
[TD="align: left"]Local[/TD]
[TD="align: left"]NE[/TD]
[TD="align: left"]Manufacturer[/TD]
[TD="align: left"]S002[/TD]
[TD="align: left"]BBB[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: left"]B[/TD]
[TD="align: left"]Local[/TD]
[TD="align: left"]NE[/TD]
[TD="align: left"]Manufacturer[/TD]
[TD="align: left"]S003[/TD]
[TD="align: left"]CCC[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: left"]B[/TD]
[TD="align: left"]Local[/TD]
[TD="align: left"]NE[/TD]
[TD="align: left"]Manufacturer[/TD]
[TD="align: left"]S004[/TD]
[TD="align: left"]DDD[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: left"]B[/TD]
[TD="align: left"]Local[/TD]
[TD="align: left"]NE[/TD]
[TD="align: left"]Manufacturer[/TD]
[TD="align: left"]S005[/TD]
[TD="align: left"]EEE[/TD]
[TD="align: left"]Paper[/TD]
[TD="align: left"]PM[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: left"]B[/TD]
[TD="align: left"]Local[/TD]
[TD="align: left"]NE[/TD]
[TD="align: left"]Manufacturer[/TD]
[TD="align: left"]S006[/TD]
[TD="align: left"]FFF[/TD]
[TD="align: left"]Paper[/TD]
[TD="align: left"]PM[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: left"]C[/TD]
[TD="align: left"]Global[/TD]
[TD="align: left"]CE[/TD]
[TD="align: left"]Trader[/TD]
[TD="align: left"]S001[/TD]
[TD="align: left"]AAA[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: left"]C[/TD]
[TD="align: left"]Global[/TD]
[TD="align: left"]CE[/TD]
[TD="align: left"]Trader[/TD]
[TD="align: left"]S002[/TD]
[TD="align: left"]BBB[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: left"]C[/TD]
[TD="align: left"]Global[/TD]
[TD="align: left"]CE[/TD]
[TD="align: left"]Trader[/TD]
[TD="align: left"]S003[/TD]
[TD="align: left"]CCC[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: left"]C[/TD]
[TD="align: left"]Global[/TD]
[TD="align: left"]CE[/TD]
[TD="align: left"]Trader[/TD]
[TD="align: left"]S004[/TD]
[TD="align: left"]DDD[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: left"]C[/TD]
[TD="align: left"]Global[/TD]
[TD="align: left"]CE[/TD]
[TD="align: left"]Trader[/TD]
[TD="align: left"]S005[/TD]
[TD="align: left"]EEE[/TD]
[TD="align: left"]Paper[/TD]
[TD="align: left"]PM[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: left"]C[/TD]
[TD="align: left"]Global[/TD]
[TD="align: left"]CE[/TD]
[TD="align: left"]Trader[/TD]
[TD="align: left"]S006[/TD]
[TD="align: left"]FFF[/TD]
[TD="align: left"]Paper[/TD]
[TD="align: left"]PM[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: left"]D[/TD]
[TD="align: left"]Global[/TD]
[TD="align: left"]CE[/TD]
[TD="align: left"]Whole Sale[/TD]
[TD="align: left"]S001[/TD]
[TD="align: left"]AAA[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: left"]D[/TD]
[TD="align: left"]Global[/TD]
[TD="align: left"]CE[/TD]
[TD="align: left"]Whole Sale[/TD]
[TD="align: left"]S002[/TD]
[TD="align: left"]BBB[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: left"]D[/TD]
[TD="align: left"]Global[/TD]
[TD="align: left"]CE[/TD]
[TD="align: left"]Whole Sale[/TD]
[TD="align: left"]S003[/TD]
[TD="align: left"]CCC[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: left"]D[/TD]
[TD="align: left"]Global[/TD]
[TD="align: left"]CE[/TD]
[TD="align: left"]Whole Sale[/TD]
[TD="align: left"]S004[/TD]
[TD="align: left"]DDD[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: left"]D[/TD]
[TD="align: left"]Global[/TD]
[TD="align: left"]CE[/TD]
[TD="align: left"]Whole Sale[/TD]
[TD="align: left"]S005[/TD]
[TD="align: left"]EEE[/TD]
[TD="align: left"]Paper[/TD]
[TD="align: left"]PM[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: left"]D[/TD]
[TD="align: left"]Global[/TD]
[TD="align: left"]CE[/TD]
[TD="align: left"]Whole Sale[/TD]
[TD="align: left"]S006[/TD]
[TD="align: left"]FFF[/TD]
[TD="align: left"]Paper[/TD]
[TD="align: left"]PM[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: left"]E[/TD]
[TD="align: left"]Others[/TD]
[TD="align: left"]S[/TD]
[TD="align: left"]Mini Mart[/TD]
[TD="align: left"]S001[/TD]
[TD="align: left"]AAA[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: left"]E[/TD]
[TD="align: left"]Others[/TD]
[TD="align: left"]S[/TD]
[TD="align: left"]Mini Mart[/TD]
[TD="align: left"]S002[/TD]
[TD="align: left"]BBB[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: left"]E[/TD]
[TD="align: left"]Others[/TD]
[TD="align: left"]S[/TD]
[TD="align: left"]Mini Mart[/TD]
[TD="align: left"]S003[/TD]
[TD="align: left"]CCC[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: left"]E[/TD]
[TD="align: left"]Others[/TD]
[TD="align: left"]S[/TD]
[TD="align: left"]Mini Mart[/TD]
[TD="align: left"]S004[/TD]
[TD="align: left"]DDD[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: left"]E[/TD]
[TD="align: left"]Others[/TD]
[TD="align: left"]S[/TD]
[TD="align: left"]Mini Mart[/TD]
[TD="align: left"]S005[/TD]
[TD="align: left"]EEE[/TD]
[TD="align: left"]Paper[/TD]
[TD="align: left"]PM[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: left"]E[/TD]
[TD="align: left"]Others[/TD]
[TD="align: left"]S[/TD]
[TD="align: left"]Mini Mart[/TD]
[TD="align: left"]S006[/TD]
[TD="align: left"]FFF[/TD]
[TD="align: left"]Paper[/TD]
[TD="align: left"]PM[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: left"]E[/TD]
[TD="align: left"]Others[/TD]
[TD="align: left"]S[/TD]
[TD="align: left"]Mini Mart[/TD]
[TD="align: left"]S001[/TD]
[TD="align: left"]AAA[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: left"]E[/TD]
[TD="align: left"]Others[/TD]
[TD="align: left"]S[/TD]
[TD="align: left"]Mini Mart[/TD]
[TD="align: left"]S002[/TD]
[TD="align: left"]BBB[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: left"]E[/TD]
[TD="align: left"]Others[/TD]
[TD="align: left"]S[/TD]
[TD="align: left"]Mini Mart[/TD]
[TD="align: left"]S003[/TD]
[TD="align: left"]CCC[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: left"]E[/TD]
[TD="align: left"]Others[/TD]
[TD="align: left"]S[/TD]
[TD="align: left"]Mini Mart[/TD]
[TD="align: left"]S004[/TD]
[TD="align: left"]DDD[/TD]
[TD="align: left"]Chemical[/TD]
[TD="align: left"]RM[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: left"]E[/TD]
[TD="align: left"]Others[/TD]
[TD="align: left"]S[/TD]
[TD="align: left"]Mini Mart[/TD]
[TD="align: left"]S005[/TD]
[TD="align: left"]EEE[/TD]
[TD="align: left"]Paper[/TD]
[TD="align: left"]PM[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: left"]E[/TD]
[TD="align: left"]Others[/TD]
[TD="align: left"]S[/TD]
[TD="align: left"]Mini Mart[/TD]
[TD="align: left"]S006[/TD]
[TD="align: left"]FFF[/TD]
[TD="align: left"]Paper[/TD]
[TD="align: left"]PM[/TD]
[/TR]
</tbody>[/TABLE]


Thank you so much for your help.

Cheers.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
I wrote a bit of VBA. I assume:
  • the two important pivot tables are on the same worksheet,
  • there is only the one header row in the pivot tables,
  • that the pivot tables can be reliably identified by the labels in their top left cells,
  • and any other pivot tables in that worksheet don't have the same labels in their top left cells.

Make the worksheet with these two pivot tables the active sheet, and run this code.

Code: 
Sub ProcessPivots()
  Dim ws1 As Worksheet
  Set ws1 = ActiveSheet
  
  ' identify pivot tables
  Dim pt1 As PivotTable, pt2 As PivotTable, pt As PivotTable
  For Each pt In ws1.PivotTables
    If pt.TableRange1.Cells(1, 1).Value = "Shipto" Then
      Set pt1 = pt
    ElseIf pt.TableRange1.Cells(1, 1).Value = "Mat Code" Then
      Set pt2 = pt
    End If
  Next
  If pt1 Is Nothing Or pt2 Is Nothing Then
    MsgBox "Couldn't identify both pivot tables.", vbCritical
    GoTo ExitSub
  End If
  
  ' insert new sheet
  Dim ws2 As Worksheet
  Set ws2 = Worksheets.Add(After:=ws1)
  
  'set up ranges
  Dim ptrange1 As Range, ptrange2 As Range
  Set ptrange1 = pt1.TableRange1
  Set ptrange2 = pt2.TableRange1
  Dim rows1 As Long, rows2 As Long
  rows1 = ptrange1.Rows.Count - 1
  rows2 = ptrange2.Rows.Count - 1
  Dim columns1 As Long, columns2 As Long
  columns1 = ptrange1.Columns.Count
  columns2 = ptrange2.Columns.Count
  
  ' headers
  ws2.Range("A1").Resize(, columns1).Value = ptrange1.Rows(1).Value
  ws2.Range("A1").Offset(, columns1).Resize(, columns2).Value = ptrange2.Rows(1).Value
  
  ' populate
  Dim ptrange2a As Range
  Set ptrange2a = ptrange2.Offset(1).Resize(rows2)
  Dim loop1 As Long
  For loop1 = 1 To rows1
    ws2.Range("A1").Offset(1 + (loop1 - 1) * rows2).Resize(rows2, columns1).Value = ptrange1.Rows(1 + loop1).Value
    ws2.Range("A1").Offset(1 + (loop1 - 1) * rows2, columns1).Resize(rows2, columns2).Value = ptrange2a.Value
  Next
  
ExitSub:
End Sub
 
Upvote 0
Hello,

Thank you for your help here, some clarification as I try it and it encounter error "MsgBox "Couldn't identify both pivot tables.", vbCritical"
- Do I have to change the name of pivot table to pt1 and pt2
- The 1st column in pt1 "Ship-to" is located in cell A9, it is impacted in above code is it to do with Range
- The 1st column in pt2 "Mat Code" is located in cell Q12, it is impacted in above code is it to do with Range
Thanks
 
Upvote 0
pt1 and pt2 are the variables used by VBA. I tried to make it easy, so you didn't have to rename pivot tables.

The two pivot tables must have "Shipto" and "Mat Code" in their respective top left cells, as your first post showed. If one has "Ship-to" instead, the code will not find it.

If you want to rename the pivot tables, it may make the code generally more reliable (though if the condition in the above sentence is followed exactly, my code is reliable), name them "Pivot A" and "Pivot B" the way you labeled them in your first post. Then this code should work:

Code: 
Sub ProcessPivots()
  Dim ws1 As Worksheet
  Set ws1 = ActiveSheet
  
  ' identify pivot tables
  Dim pt1 As PivotTable, pt2 As PivotTable
  On Error Resume Next
  Set pt1 = ws1.PivotTables("Pivot A")
  Set pt2 = ws1.PivotTables("Pivot B")
  On Error Goto 0
  If pt1 Is Nothing Or pt2 Is Nothing Then
    MsgBox "Couldn't identify both pivot tables.", vbCritical
    GoTo ExitSub
  End If
  
  ' insert new sheet
  Dim ws2 As Worksheet
  Set ws2 = Worksheets.Add(After:=ws1)
  
  'set up ranges
  Dim ptrange1 As Range, ptrange2 As Range
  Set ptrange1 = pt1.TableRange1
  Set ptrange2 = pt2.TableRange1
  Dim rows1 As Long, rows2 As Long
  rows1 = ptrange1.Rows.Count - 1
  rows2 = ptrange2.Rows.Count - 1
  Dim columns1 As Long, columns2 As Long
  columns1 = ptrange1.Columns.Count
  columns2 = ptrange2.Columns.Count
  
  ' headers
  ws2.Range("A1").Resize(, columns1).Value = ptrange1.Rows(1).Value
  ws2.Range("A1").Offset(, columns1).Resize(, columns2).Value = ptrange2.Rows(1).Value
  
  ' populate
  Dim ptrange2a As Range
  Set ptrange2a = ptrange2.Offset(1).Resize(rows2)
  Dim loop1 As Long
  For loop1 = 1 To rows1
    ws2.Range("A1").Offset(1 + (loop1 - 1) * rows2).Resize(rows2, columns1).Value = ptrange1.Rows(1 + loop1).Value
    ws2.Range("A1").Offset(1 + (loop1 - 1) * rows2, columns1).Resize(rows2, columns2).Value = ptrange2a.Value
  Next
  
ExitSub:
End Sub
 
Upvote 0
Hello Jon,

Thank you for helping me, it work great now. Few small things. when it create a new sheet. It actually copy the header of Pivot 1 and value of Pivot 2 columns like below. I dont need header to be there, just need values from the pivot table to be copy. HOw to change the code on this. Also I would like the data to be inserted into existing sheet name "Data" starting row 59.

Thank you for your help.

[TABLE="width: 615"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: left"]Customer[/TD]
[TD="align: left"]Name 1[/TD]
[TD="align: left"]Sales Group[/TD]
[TD="align: left"]Matcode[/TD]
[TD="align: left"]Description EN[/TD]
[/TR]
[TR]
[TD="align: left"]Customer[/TD]
[TD="align: left"]Name 1[/TD]
[TD="align: left"]Sales Group[/TD]
[TD="align: left"]S100003500[/TD]
[TD="align: left"]น้ำตาลทรายดิบ A (เทกอง)Bonsucro[/TD]
[/TR]
[TR]
[TD="align: left"]Customer[/TD]
[TD="align: left"]Name 1[/TD]
[TD="align: left"]Sales Group[/TD]
[TD="align: left"]S100009800[/TD]
[TD="align: left"]Raw A Sugar (Bulk) OFF SPEC[/TD]
[/TR]
[TR]
[TD="align: left"]Customer[/TD]
[TD="align: left"]Name 1[/TD]
[TD="align: left"]Sales Group[/TD]
[TD="align: left"]S100009900[/TD]
[TD="align: left"]น้ำตาลทรายดิบ A (เทกอง)[/TD]
[/TR]
[TR]
[TD="align: left"]Customer[/TD]
[TD="align: left"]Name 1[/TD]
[TD="align: left"]Sales Group[/TD]
[TD="align: left"]S100109900[/TD]
[TD="align: left"]น้ำตาลทรายดิบ (1000KG)NoMark[/TD]
[/TR]
[TR]
[TD="align: left"]Customer[/TD]
[TD="align: left"]Name 1[/TD]
[TD="align: left"]Sales Group[/TD]
[TD="align: left"]S100120000[/TD]
[TD="align: left"]น้ำตาลทรายดิบมิตรผล (1000KG)[/TD]
[/TR]
[TR]
[TD="align: left"]Customer[/TD]
[TD="align: left"]Name 1[/TD]
[TD="align: left"]Sales Group[/TD]
[TD="align: left"]S100220000[/TD]
[TD="align: left"]น้ำตาลทรายดิบ A (500KG)[/TD]
[/TR]
[TR]
[TD="align: left"]Customer[/TD]
[TD="align: left"]Name 1[/TD]
[TD="align: left"]Sales Group[/TD]
[TD="align: left"]S100310000[/TD]
[TD="align: left"]น้ำตาลทรายดิบ A มิตรผล (50KG.)[/TD]
[/TR]
[TR]
[TD="align: left"]Customer[/TD]
[TD="align: left"]Name 1[/TD]
[TD="align: left"]Sales Group[/TD]
[TD="align: left"]S110009900[/TD]
[TD="align: left"]น้ำตาลทรายดิบ B (เทกอง)[/TD]
[/TR]
[TR]
[TD="align: left"]Customer[/TD]
[TD="align: left"]Name 1[/TD]
[TD="align: left"]Sales Group[/TD]
[TD="align: left"]S110220000[/TD]
[TD="align: left"]น้ำตาลทรายดิบ B (500KG)[/TD]
[/TR]
[TR]
[TD="align: left"]Customer[/TD]
[TD="align: left"]Name 1[/TD]
[TD="align: left"]Sales Group[/TD]
[TD="align: left"]S13000990[/TD]
[TD="align: left"]น้ำตาลทรายดิบ (เทกอง)[/TD]
[/TR]
[TR]
[TD="align: left"]Customer[/TD]
[TD="align: left"]Name 1[/TD]
[TD="align: left"]Sales Group[/TD]
[TD="align: left"]S130009900[/TD]
[TD="align: left"]น้ำตาลทรายดิบไฮโพล์มิตรผล (เทกอง)[/TD]
[/TR]
[TR]
[TD="align: left"]Customer[/TD]
[TD="align: left"]Name 1[/TD]
[TD="align: left"]Sales Group[/TD]
[TD="align: left"]S130110000[/TD]
[TD="align: left"]น้ำตาลทรายดิบไฮโพล์มิตรผล (1000KG.)[/TD]
[/TR]
[TR]
[TD="align: left"]Customer[/TD]
[TD="align: left"]Name 1[/TD]
[TD="align: left"]Sales Group[/TD]
[TD="align: left"]S130129900[/TD]
[TD="align: left"]น้ำตาลทรายดิบไฮโพล์ NoMark (1000KG)[/TD]
[/TR]
[TR]
[TD="align: left"]Customer[/TD]
[TD="align: left"]Name 1[/TD]
[TD="align: left"]Sales Group[/TD]
[TD="align: left"]S130310000[/TD]
[TD="align: left"]น้ำตาลทรายดิบไฮโพล์มิตรผล (50KG.)[/TD]
[/TR]
[TR]
[TD="align: left"]Customer[/TD]
[TD="align: left"]Name 1[/TD]
[TD="align: left"]Sales Group[/TD]
[TD="align: left"]S130510070[/TD]
[TD="align: left"]น้ำตาลทรายดิบไฮโพล์มิตรผล(25KG)REG-KOREA[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
These are minor modifications. As you become familiar with how VBA works, you'll be able to figure them out for yourself.

In the code below I've commented out the old lines and inserted the new lines.

Code: 
Sub ProcessPivots()
  Dim ws1 As Worksheet
  Set ws1 = ActiveSheet
  
  ' identify pivot tables
  Dim pt1 As PivotTable, pt2 As PivotTable
  On Error Resume Next
  Set pt1 = ws1.PivotTables("Pivot A")
  Set pt2 = ws1.PivotTables("Pivot B")
  On Error Goto 0
  If pt1 Is Nothing Or pt2 Is Nothing Then
    MsgBox "Couldn't identify both pivot tables.", vbCritical
    GoTo ExitSub
  End If
  
  ' insert new sheet
  Dim ws2 As Worksheet
  ''Set ws2 = Worksheets.Add(After:=ws1) '' OLD
  Set ws2 = Worksheets("Data") '' NEW
  
  'set up ranges
  Dim ptrange1 As Range, ptrange2 As Range
  Set ptrange1 = pt1.TableRange1
  Set ptrange2 = pt2.TableRange1
  Dim rows1 As Long, rows2 As Long
  rows1 = ptrange1.Rows.Count - 1
  rows2 = ptrange2.Rows.Count - 1
  Dim columns1 As Long, columns2 As Long
  columns1 = ptrange1.Columns.Count
  columns2 = ptrange2.Columns.Count
  
  '' headers '' NO LONGER USED
  ''ws2.Range("A1").Resize(, columns1).Value = ptrange1.Rows(1).Value '' OLD
  ''ws2.Range("A1").Offset(, columns1).Resize(, columns2).Value = ptrange2.Rows(1).Value '' OLD
  
  ' populate
  Dim ptrange2a As Range
  Set ptrange2a = ptrange2.Offset(1).Resize(rows2)
  Dim loop1 As Long
  For loop1 = 1 To rows1
    ''ws2.Range("A1").Offset(1 + (loop1 - 1) * rows2).Resize(rows2, columns1).Value = ptrange1.Rows(1 + loop1).Value '' OLD
    ''ws2.Range("A1").Offset(1 + (loop1 - 1) * rows2, columns1).Resize(rows2, columns2).Value = ptrange2a.Value '' OLD
    ws2.Range("A59").Offset((loop1 - 1) * rows2).Resize(rows2, columns1).Value = ptrange1.Rows(1 + loop1).Value '' NEW
    ws2.Range("A59").Offset((loop1 - 1) * rows2, columns1).Resize(rows2, columns2).Value = ptrange2a.Value '' NEW
  Next
  
ExitSub:
End Sub
 
Upvote 0
Hi,

The head values still there.... As Customer, Sale group, Matcode Description EN, i think it is to do with populate part of the code....can you help thank you so much.

[TABLE="width: 716"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: left"]Customer[/TD]
[TD="align: left"]Name 1[/TD]
[TD="align: left"]Sales Group[/TD]
[TD="align: left"]Matcode[/TD]
[TD="align: left"]Description EN[/TD]
[/TR]
[TR]
[TD="align: left"]Customer[/TD]
[TD="align: left"]Name 1[/TD]
[TD="align: left"]Sales Group[/TD]
[TD="align: left"]S133071400[/TD]
[TD="align: left"]น้ำตาลซองแดงเอราวัณ SACHET(7G.*100*20)[/TD]
[/TR]
[TR]
[TD="align: left"]Customer[/TD]
[TD="align: left"]Name 1[/TD]
[TD="align: left"]Sales Group[/TD]
[TD="align: left"]S133071500[/TD]
[TD="align: left"]น้ำตาลซองแดงSPASSO SACHET (7G.*100*20)[/TD]
[/TR]
[TR]
[TD="align: left"]Customer[/TD]
[TD="align: left"]Name 1[/TD]
[TD="align: left"]Sales Group[/TD]
[TD="align: left"]S303070400[/TD]
[TD="align: left"]น้ำตาลบริสุทธิ์การบินไทย (7G.*100*20)[/TD]
[/TR]
[TR]
[TD="align: left"]Customer[/TD]
[TD="align: left"]Name 1[/TD]
[TD="align: left"]Sales Group[/TD]
[TD="align: left"]S303072300[/TD]
[TD="align: left"]น้ำตาลบริสุทธิ์ Tables T1(7G*100*20)[/TD]
[/TR]
[TR]
[TD="align: left"]Customer[/TD]
[TD="align: left"]Name 1[/TD]
[TD="align: left"]Sales Group[/TD]
[TD="align: left"]S403070400[/TD]
[TD="align: left"]น้ำตาลบริสุทธิ์ซองการบินไทย (7G.*100*20)[/TD]
[/TR]
[TR]
[TD="align: left"]Customer[/TD]
[TD="align: left"]Name 1[/TD]
[TD="align: left"]Sales Group[/TD]
[TD="align: left"]S403071400[/TD]
[TD="align: left"]น้ำตาลบริสุทธิ์เอราวัณSACHET(7G.*100*20)[/TD]
[/TR]
[TR]
[TD="align: right"]100013[/TD]
[TD="align: left"]บริษัท เนสท์เล่ (ไทย) จำกัด[/TD]
[TD="align: left"]D11[/TD]
[TD="align: left"]Matcode[/TD]
[TD="align: left"]Description EN[/TD]
[/TR]
[TR]
[TD="align: right"]100013[/TD]
[TD="align: left"]บริษัท เนสท์เล่ (ไทย) จำกัด[/TD]
[TD="align: left"]D11[/TD]
[TD="align: left"]S133071400[/TD]
[TD="align: left"]น้ำตาลซองแดงเอราวัณ SACHET(7G.*100*20)[/TD]
[/TR]
[TR]
[TD="align: right"]100013[/TD]
[TD="align: left"]บริษัท เนสท์เล่ (ไทย) จำกัด[/TD]
[TD="align: left"]D11[/TD]
[TD="align: left"]S133071500[/TD]
[TD="align: left"]น้ำตาลซองแดงSPASSO SACHET (7G.*100*20)[/TD]
[/TR]
[TR]
[TD="align: right"]100013[/TD]
[TD="align: left"]บริษัท เนสท์เล่ (ไทย) จำกัด[/TD]
[TD="align: left"]D11[/TD]
[TD="align: left"]S303070400[/TD]
[TD="align: left"]น้ำตาลบริสุทธิ์การบินไทย (7G.*100*20)[/TD]
[/TR]
[TR]
[TD="align: right"]100013[/TD]
[TD="align: left"]บริษัท เนสท์เล่ (ไทย) จำกัด[/TD]
[TD="align: left"]D11[/TD]
[TD="align: left"]S303072300[/TD]
[TD="align: left"]น้ำตาลบริสุทธิ์ Tables T1(7G*100*20)[/TD]
[/TR]
[TR]
[TD="align: right"]100013[/TD]
[TD="align: left"]บริษัท เนสท์เล่ (ไทย) จำกัด[/TD]
[TD="align: left"]D11[/TD]
[TD="align: left"]S403070400[/TD]
[TD="align: left"]น้ำตาลบริสุทธิ์ซองการบินไทย (7G.*100*20)[/TD]
[/TR]
[TR]
[TD="align: right"]100013[/TD]
[TD="align: left"]บริษัท เนสท์เล่ (ไทย) จำกัด[/TD]
[TD="align: left"]D11[/TD]
[TD="align: left"]S403071400[/TD]
[TD="align: left"]น้ำตาลบริสุทธิ์เอราวัณSACHET(7G.*100*20)[/TD]
[/TR]
[TR]
[TD="align: right"]100015[/TD]
[TD="align: left"]บริษัท ควอลิตี้ คอฟฟี่ โปรดักท์ส[/TD]
[TD="align: left"]D11[/TD]
[TD="align: left"]Matcode[/TD]
[TD="align: left"]Description EN[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Do your pivot tables have more than one row of headers at the top? This was one of the assumptions, based on your initial post. I built these two pivot tables based on that post:

NvKXwZC.png
 
Last edited:
Upvote 0
Hi,

I think it has 2 header rows, one is empty and below it is header name...and I remove it in Pivot table and it worked now thank you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Copy Data from 2 pivot table and make a new data set......
Replies
0
Views
502
sasils
S

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top