Macros & pivot tables

One of the "vb people" will be along shortly (I hope) to give you a good answer, but until then...

try this incredibly hacky work-around!
Have you considered dedicating a sheet to imported access data? If you can tolerate having your data come in "backwards" that is most recently imported in the A and B columns, try this.

Have a workbook with three sheets labeled "imported", "raw", and "pivot results". Make the pivot table in "imported" by hand once, referencing columns a and b of "imported". For this example say the pivot table output you want is in columns D and E of "imported". Import the access data into "imported" columns a and b.

Then record a macro that does the following:
go to sheet "transfer"
copy columns A and B
go to sheet "raw"
paste at raw A1
select raw!A:B and insert columns
go to sheet "imported"
copy columns D and E
go to sheet "pivot results"
paste as values at A1
select pivot results!A:B and insert columns
go to sheet "imported"
select imported!A:B and clear contents

Now you have your access data in "raw", your pivot data in "pivot data" and the "transfer" sheet is cleared for the next time. Additionally the macro's 'target' columns in both both the "raw" and "pivot data" sheets are cleared and ready for the next transfer.
Hope that helps

Hi Cat,

If you would like to post or e-mail me the code you get from recording your pivot table setup, I will modify it to work on any worksheet.

Damon