Excel VBA: Update Dynamic Data Range in PivotTable After Removing Duplicates & Columns

Hello,

At work, I extract a report in Excel with 35 columns (A-AI) and a variable number of rows. I created an Excel file to analyze this data, so one tab contains the pasted data and several other tables and pivot charts based on them. As not all columns from the report are needed by me, I created a macro that:
- removes duplicates
- removes unnecessary columns
- Updates all pivot tables

Finally, it obtains a table with 17 columns (A to Q) as the basis for the pivot tables. And here I have a problem.
When I create a PivotTable, I have a data range: 'Data to analyze'! $ A $ 1: $ Q $ 229.
Unfortunately, after uploading new data to the tab and running the macro, the scope changes to "Data for analysis"! $ A $ 1: $ G $ 697.
Why does the table data range change (omit HQ columns)?
The macro looks like this:

Better to make a separate worksheet for PivotTable data and re-copy the data you want instead of deleting the columns that the PivotTable is already cached

Thank you very much. I did it and it works. I have one more question. I would like to sort data with a macro, but unfortunately I get an error. I have a table always structured in the same way, but the sheet in which it is located has different names. I tried to remove Worksheets ("reporting 01/27"), changed ActiveWorkbook.Worksheets ("reporting 01/27") to ThisWorkbook did nothing. How do I fix a macro to sorot data in the sheet it was included in, even if the name changes (reporting 27.01, reporting 28.01)?


ActiveWorkbook.Worksheets ("reporting 01/27"). Sort.SortFields.Clear
ActiveWorkbook.Worksheets ("reporting 27.01"). Sort.SortFields.Add Key: = Range (_
"C2: C469"), SortOn: = xlSortOnValues, Order: = xlAscending, DataOption: = _
xlSortNormal
With ActiveWorkbook.Worksheets ("reporting 01/27"). Sort
.SetRange Range ("A1: AI469")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

instead of ActiveWorkbook.Worksheets ("reporting 27.01")
simply ActiveSheet

ps ThisWorkbook is the entire workbook in which the macro

Cool thanks. That's one more question. I would like to remove all spaces from column AE. But I have a bug with the command cell = Trim (cell) How can I fix it?

cell.text=trim(cell.text)

Or

@JRV
Mam błąd dotyczący linii cell.Text = Trim(cell.Text)
Run time error „1004” Unable to set the text property of range class

@clubs
Mam błąd dotyczący linii .Value = Application.Trim(.Value)
Run timer error “13” Type mismatch

zmien
For Each cell In Columns("AE")
na
For Each cell In Columns("AE").cells

Or

clubs wrote:

Or

Code: VB.net

Log in, to see the code

Thank you both, but only the macro above works.

Hello

It will also work, but in this form


I just don't know why you refer to the entire AE column, where in Excel 2010 there are 1048576 rows and the macro has to "grind" the whole range, it is better to set a specific range or the range used, then the macro will work much faster