Need Help Updating Pivot Table Source Data

**walt737** · 02-22-2019, 12:06 AM

So I'm brand new to VBA and having a hard time figuring out how to use a macro to update the source data on an existing pivot table.
I have data in columns A:AX, AX contains dates and I have the macro sort them leaving the blanks on bottom. I want the macro to select the source data from A1 to the last row containing data in AX. I want to do this without deleting the rows containing blank AX cells.

This is what I have so far. No clue if I'm close or completely wrong.

Please Login or Register  to view this content.

EDIT* This pivot groups by dates and it won't let me do that if there are blanks. If I use a table it still pulls in blanks and ungroups my dates

**kersplash** · 02-22-2019, 01:14 AM

You don't need a macro to update the source of a pivot table.

Put the source data in a table and create the pivot. The source expands and contracts with the table.

**walt737** · 02-22-2019, 08:34 AM

Thanks! I didn't know about using Tables. That will come in handy.

Sorry I should have been more clear about why I want to update the source data. This pivot groups by dates and it won't let me do that if there are blanks. If I use a table it still pulls in blanks and ungroups my dates. I'm not sure if there is a workaround for that w/o updating the source data each time.

**kersplash** · 02-22-2019, 10:09 AM

Your pivot table should allow blanks in dates and still group. Are they really blanks or maybe "" from a formula?

You might need to attach a sample workbook.

**walt737** · 02-25-2019, 08:27 AM

You're right. They are "" from a formula. Not sure if its the most efficient way, but I just added a line to clear contents from the AX column if the cell = "". After that the above code worked for me.

Need Help Updating Pivot Table Source Data

LinkBack

Thread Tools

Rate This Thread

Display

Need Help Updating Pivot Table Source Data

Re: Need Help Updating Pivot Table Source Data

Re: Need Help Updating Pivot Table Source Data

Re: Need Help Updating Pivot Table Source Data

Re: Need Help Updating Pivot Table Source Data

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Updating the source data of a Pivot Table

Multiple Pivot table in different tabs from one pivot source(source data extended to colum

Updating Pivot Source Data Automatically

[SOLVED] Updating data source and refreshing all pivot tables through VBA.

[SOLVED] Problem updating source for pivot table

updating pivot tables using dynamic data source

Updating source data while in Pivot Table

Bookmarks

Bookmarks

Posting Permissions