RE: copy paste entire pivot data Using VBA

[Keerthi@21]

hi p45cal,

Original 1.PNG

The code works completely fine now in dummy data which i shared with you. Attached the image of original data pivot headers for your reference.


When i applied the same code in original data facing error. I have also changed the range of pivot from A3 to A4 in the first line of your code. Also, renamed the units column with name "TP". still Facing error at the line highlighted below:


UnitsColm = Application.Match("TP", Destn1.Rows(1), 0) 'This just determines which column is going to be missed out; it looks for 'Units' in the first row of the first copied range -> Replaced units with TP column name
SourceRng.Copy Destn2 ' Need to understand this line what it does
Destn2.Columns(UnitsColm).ClearContents -> Facing error here -> Run time error '13' Type mistmatch
For Each cll In Destn2.Columns(Destn2.Columns.Count).Cells 'take each cell in the last column of the newly copied data (assumes that the Total column is the last column).
If IsNumeric(cll.Value) And Len(cll.Value) > 0 Then cll.Value = Abs(cll.Value) 'if the value is numeric and not empty and less than 0 then multiply by -1 to flip from negative to positive.
Next cll
End Sub


Kindly help out. Hope this would be the final help required from my end in this thread.


Thanks for your support in advance.