macro for geting data from pivot table (getdata)

Dear brother and sister,

I have a new weekly task. I have a pivot table and one summary file (PCT-AFTER,xls). I asked here before, the forum is very helpful.

Task:
I need to get some data from pivot table and copied to the summary file according to specific account managers.

How can I modify the following code, therefore, I can copy data from pivot table to summary file correctly?

If you following code, it will shows the message : "Pivot table for processing is not found on activesheet of this workbook", I quite sure the sheet is "AD".


LMR file is pivot table
PCT-After.xls is summary file

My macro knowledge is week, I tried to modify for more than 10 times, still doesn't work. Please help me.

Option Explicit
Sub test()
Dim PCT As Workbook, main As Workbook, z(1 To 1, 1 To 5), i As Long, pctn, idate, pt As PivotTable, c, firstaddress As String, temp, irow As Long
On Error Resume Next: Set PCT = Workbooks("PCT-after.xls")
irow = Application.InputBox("Please enter row number for PCT-enter.xls to input data (same row is used for all students)", Type:=1)
If irow = 0 Then Exit Sub
If Err.Number <> 0 Then
    MsgBox "Please open PCT-after.xls file and try again", vbCritical: Exit Sub
End If: Set main = ActiveWorkbook: Set pt = main.ActiveSheet.AD
If Err.Number <> 0 Then
    MsgBox "Pivot table for processing is not found on activesheet of this workbook", vbCritical: Exit Sub
End If: Application.ScreenUpdating = False
With PCT.Sheets("Name List"): pctn = .Range(.[a5], .Cells(Rows.Count, "a").End(xlUp)): End With
With main.Sheets(1)
    idate = .[a1:a999].Find("Report Date:", , xlValues, xlWhole).Offset(, 1):
        With pt
            For i = 1 To UBound(pctn)
                Set c = .TableRange1.Find(pctn(i, 1), , xlValues, xlPart)
                    If Not c Is Nothing Then
                        firstaddress = c.Address
                        Do
                            If c.PivotCell.PivotCellType = xlPivotCellSubtotal Then
                                temp = Trim(pctn(i, 1)) & " 99999*": z(1, 1) = c.Offset(, 2): z(1, 2) = c.Offset(, 3)
                                z(1, 3) = .GetData("' % Primary sales of closed leads' 'Campaign Name' 'maturing mortgage' 'RM Name'" & temp)
                                z(1, 4) = .GetData("'% Primary sales of closed leads' 'Campaign Name' 'maturing term deposit' 'RM Name'" & temp)
                                z(1, 5) = .GetData("'% Primary sales of closed leads' 'Campaign Name' 'signficiant deposit' 'RM Name'" & temp)
                                    With PCT.Sheets(Trim(pctn(i, 1))): With .Cells(irow, 1)
                                            .Value = "'" & idate: With .Offset(, 25).Resize(, 5): .Value = z: .NumberFormat = "0.00%": End With
End With: End With: End If: Set c = .TableRange1.FindNext(c): Loop Until c Is Nothing Or c.Address = firstaddress
End If: Next: End With: End With: Application.ScreenUpdating = True: End Sub

[code]
irow = Application.InputBox("Please enter row number for PCT.xls to input data (same row is used for all students)", Type:=1)
If irow = 0 Then Exit Sub
/code]

after I also add

irow As Long

it still doesn't work...


I would like to chose which row of PCT-after.xls should be placed.

Previously, there is missing macro. I tried to add it back to your attached macro, it says that variable is not defined. I think it can just add part of macro to attached macro, why it is unsuccessful? Thanks

I have read the 3 condition.

However, as I copy the macro statement to my file at office, it has problem.
I follow your step, PCt-After.xls is opened.
Pivot table file,LMR _8June.xls is also opened.

I not understand :
1. Workbook with pivot MUST be active
2.While workbook with pivot is being active run code "test"
What is meaning of pivot table active? As my LMR _8June.xls is opened, is it mean "active"? I search in google, there are different answer, someone told it should be active cell.

After I use the code in my office, file, there are two problem,
1. the data can be copied to row 5, however, for column A (date) of PCT-after.xls, the report date can't be copied, for example, A5 become blank cell, rather than date. ( in my case at office)

2. only % of leads auto closed and % of leads unactioned can be copied.
However, other % primary sales of closed leads :maturing mortgage, maturing term deposit and signficiant deposit can;t be copied.
I just copied I28 (% primary sales of closed leads)of pivot table file to macro statement, however, it still doesn't work. I hope to ensure the name fit with macro

I do it several time for testing, after testing, those maturing mortage etc. data can't be copied.

I can't upload the real file because it is company data, I make the sample as same as to real file. (every thing is the same). I find that may be field name has problem. (for example, spacing of % primary sales of closed lead between real file and sample).

I believe the following code may need to be changed. How can I make sure macro can cope with the field name (% primary sales of closed lead)?

 If c.PivotCell.PivotCellType = xlPivotCellSubtotal Then
                                z(1, 1) = c.Offset(, 3): z(1, 2) = c.Offset(, 5)
                                z(1, 3) = .GetData("' % primary sales of closed leads' 'Campaign Name' 'maturing mortgage' 'RM Name'" & Trim(pctn(i, 1)) & " 99999*")
                                z(1, 4) = .GetData("' % Primary sales of closed leads' 'Campaign Name' 'maturing term deposit' 'RM Name'" & Trim(pctn(i, 1)) & " 99999*")
                                z(1, 5) = .GetData("' % Primary sales of closed leads' 'Campaign Name' 'signficiant deposit' 'RM Name'" & Trim(pctn(i, 1)) & " 99999*")
                                    With PCT.Sheets(Trim(pctn(i, 1)))
                                        With .Cells(irow, 1): .Value = "'" & idate: With .Offset(, 26).Resize(, 5): .Value = z: .NumberFormat = "0.00%"
                        End With: End With: End With: End If: Set c = .TableRange1.FindNext(c): Loop Until c Is Nothing Or c.Address = firstaddress
End If: Next: End With: End With: Application.ScreenUpdating = True: End Sub

attached file (pivot table file) , other % primary sales of closed leads :maturing mortgage, maturing term deposit and signficiant deposit can;t be copied.

hi, ronlau123,

1. What does it mean that a workbook is active?
Excel is closed, there are no open workbooks. You open file "Book1.xls", you see on the screen - it means it's active. Then you open file "Book2.xls", you see it on the screen - it means Book2 is active and Book1 is inactive. So both books are open but at any given time only one workbook can be active.
In other words active workbook means a workbook that you will enter info in if you press any letter on your keyboard.
Hope this is clear now. So in terms of the file with pivot, you need to see pivot on the screen and if you type anything it will appear on the sheet with pivot. Then you run "test" code (PCT-after.xls must be open or you'll be asked to do that).

2. As soon as you will have differences in header names you will have this problem.
Example:
" % of leads auto closed " - leading space and end space - now set in the code to work in your sample files
" % of leads auto closed" - leading space
"% of leads auto closed" - no spaces before or after
These last two strings will give error as they are different from the one in the code. So the headers present here:

z(1, 3) = .GetData("'  % primary sales of closed leads ' 'Campaign Name' 'maturing mortgage' 'RM Name'" & Trim(pctn(i, 1)) & " 99999*")
z(1, 4) = .GetData("'  % primary sales of closed leads ' 'Campaign Name' 'maturing term deposit' 'RM Name'" & Trim(pctn(i, 1)) & " 99999*")
z(1, 5) = .GetData("'  % primary sales of closed leads ' 'Campaign Name' 'signficiant deposit' 'RM Name'" & Trim(pctn(i, 1)) & " 99999*")

MUST be consistent (written the same way) for any pivot table.

Hope this helps