Copy pivot table and paste it in to next available column in same sheet

[shan]

Hello,

Request your help for macro.

I would like to copy a pivot table any size and paste it on the same sheet - next available column.


Thanks
Shan

[shan]

I think i am not clear what I am looking for. Please find a file where I need to copy a Pivot table (no limit on Column and rows) in the same same sheet on the next available + 2 column.



Thanks
Shan

[p45cal]

deleted (duplicated below)

[p45cal]

try:
Sub blah()
Dim pt As PivotTable
On Error Resume Next
Set pt = Selection.PivotTable
On Error GoTo 0
If pt Is Nothing Then
MsgBox "Put the selection in/on an existing pivot table"
Else
Set yyy = pt.TableRange2
ofset = yyy.Columns.Count
Do Until Application.CountA(yyy.Offset(, ofset)) = 0
ofset = ofset + 1
Loop
yyy.Copy yyy.Offset(, ofset)
End If
End Sub


edit after posting:
Ahh, + 2 columns (I didn't see you had posted another message)
Try:
Sub blah()
Dim pt As PivotTable
On Error Resume Next
Set pt = Selection.PivotTable
On Error GoTo 0
If pt Is Nothing Then
MsgBox "Put the selection in/on an existing pivot table"
Else
Set yyy = pt.TableRange2.Resize(, pt.TableRange2.Columns.Count + 2)
ofset = pt.TableRange2.Columns.Count
Do Until Application.CountA(yyy.Offset(, ofset)) = 0
ofset = ofset + 1
Loop
pt.TableRange2.Copy yyy.Offset(, ofset).Columns(3)
End If
End Sub
It will leave two completely clear columns between subsequent copies.

[shan]

Thank you So much Sir
It working as desired!!

Have a nice day !!!

[shan]

Sorry Sir,

Addition to this how do I change Report Filter or page field for the new Pivot copied on the same sheet

[p45cal]

Sorry Sir,

Addition to this how do I change Report Filter or page field for the new Pivot copied on the same sheetThe glib answer is to use your mouse!
However, do you have lots of these to do?
If there were only one Report Filter it wouldn't be too difficult to cycle through, but there are two report filters, and it could get quite involved.

How would you like to see the fields change?

[shan]

I would like to change only one filter i.e. TYPE ... I need to change Value from TAG3 to TAG4

[p45cal]

A tweak:
Sub blah()
Dim pt As PivotTable
On Error Resume Next
Set pt = Selection.PivotTable
On Error GoTo 0
If pt Is Nothing Then
MsgBox "Put the selection in/on an existing pivot table"
Else
Set yyy = pt.TableRange2.Resize(, pt.TableRange2.Columns.Count + 2)
ofset = pt.TableRange2.Columns.Count
Do Until Application.CountA(yyy.Offset(, ofset)) = 0
ofset = ofset + 1
Loop
pt.TableRange2.Copy yyy.Offset(, ofset).Columns(3)
Set newpt = yyy.Offset(, ofset).Columns(3).PivotTable
Set pis = newpt.PivotFields("Type").PivotItems
For i = 1 To pis.Count
If pis(i).Visible Then Exit For
Next i
'Debug.Print pis(i).Name, pis(i).Visible, i, i Mod pis.Count + 1
pis(i Mod pis.Count + 1).Visible = True
pis(i).Visible = False
End If
End Sub
It will change the Type to be different from the pivot table that was selected when you run the macro.

[shan]

Thank you Sir!!

Its working perfectly as desired.