extract from pivot vba

[Sayf1]

Hey all,

so let’s say I have a pivot table that has many categories. I want to
tell the vba to onlygrab the categories in colomn A and not the blank cells and paste them in tab “sheets” in colomn s.

How would i do that?

Thank you in advance !

 

[mse330]

Hey Sayf1,

Try the below code which copies the data from 1 sheet to another without the blanks regardless whether the original data is a pivot table or not. Make sure you have a sheet called "sheets" in your file

Sub CopyFromPivot()
Dim Ws1 As Worksheet, Ws2 As Worksheet, Ar1() As Variant, Ar2() As Variant, Cnt As Long
Set Ws1 = Sheets("Sheet1") 'change the sheet name where you have your pivot table
Set Ws2 = Sheets("sheets")
Ar1 = Ws1.Range("A1", Ws1.Range("A" & Rows.Count).End(xlUp)).Value 'change the column to take data from
ReDim Ar2(1 To UBound(Ar1))
For i = LBound(Ar1) To UBound(Ar1)
    If Ar1(i, 1) <> "" Then
        Cnt = Cnt + 1
        Ar2(Cnt) = Ar1(i, 1)
    End If
Next
Ws2.Range("S1").Resize(UBound(Ar2)).Value = Application.Transpose(Ar2)
End Sub