Loss of pivot columns when using pivotcaches.create with new data source
I am faced with an issue with my data and pivot table VBA. My data now adds a new blank column on the far left. I must delete this column so that my data fits in the correct range and the column all have a header as before. When I delete this column the range of the source data changes minus the deleted column.
Questions: 1) Is there a way to prevent the data source from changing when deleting the row?
2) Is there a way to prevent the following vba from removing the missing columns in the pivot table that are not found in the new data.
ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Data2", Version:= _
This line of code seems to remove columns form the pivot for the pivot table field instead of leaving them. I have already ensured the following options are set. (Preserve cell formatting on update - is checked) and (Number of items to retain per field - is set to max) before running the code above. When the code runs it reverts the "Number of items to retain per field" back to automatic and I lose the columns.
I appreciate any and all advice. Thanks
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
By sameer79 in forum Excel Programming / VBA / Macros
Last Post: 01-28-2018, 02:50 PM
By kbdb in forum Excel Charting & Pivots
Last Post: 10-23-2017, 07:23 AM
By breadwinner in forum Excel Programming / VBA / Macros
Last Post: 07-25-2016, 01:27 AM
By Ishwarind in forum Excel Programming / VBA / Macros
Last Post: 03-07-2016, 04:01 AM
By ethelp in forum Excel Charting & Pivots
Last Post: 10-07-2014, 02:29 PM
By email@example.com in forum Excel General
Last Post: 07-12-2006, 04:40 PM
By cheerboy555 in forum Excel General
Last Post: 04-30-2006, 09:55 AM
Tags for this Thread
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1