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?
or
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:= _
6)
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
Questions: 1) Is there a way to prevent the data source from changing when deleting the row?
or
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:= _
6)
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
