MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macros & pivot tables

Posted by Cat on August 24, 2001 12:20 PM

Any way I can record a macro so that it will create a pivot table. I have a spreadsheet that contains the same columns I bring over from Access every week. I need this macro to sum the columns and not refer to the previous weeks spreadsheet. I've tried using the relative button with no luck. I'd appreciate any help.

Posted by Eric on August 24, 2001 1:23 PM

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

Posted by Damon Ostrander on August 26, 2001 5:21 PM

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.