• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy data from Pivot Table

Vijayarc

Member
Hi Ninja

i want to copy data from pivot table to my MIS sheet bases on filter condition. below is a piece of code i prepared ,
Here by i attached my file . I want to select ColumnRange + DataBodyRange without last row and last column and paste in MIS sheet
please help and thank in advance

Code:
Sub Macro1()
Sheets("sheet1").Select
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1) 'Sheets("sheet1").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("DB_Summery").ClearAllFilters
  ActiveSheet.PivotTables("PivotTable1").PivotFields("DB_Summery").CurrentPage = "Pillar 1"
  pt.ColumnRange.Resize(pt.ColumnRange.Rows.Count + 5, pt.ColumnRange.Columns.Count - 1).Select
    Selection.Copy
    Sheets("MIS").Select
    Range("F9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
[\code]
 

Attachments

  • MIS .xlsm
    315.7 KB · Views: 6
Hi,​
according to the attachment as a VBA beginner starter :​
Code:
Sub Demo1()
    With Sheet1.PivotTables(1).TableRange1.Columns
         Sheet3.[F9].Resize(.Rows.Count - 1, .Count - 2).Value2 = .Cells(2).Resize(.Rows.Count - 1, .Count - 2).Value2
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !
 
Back
Top