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.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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)?

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Hope this helps