I've written some VBA code to create a PivotTable which should be filtered automatically. There a two possible filters: 0 and 1. It is possible that both exist but it is also possible that only one of them exists.
I want Excel at first to set the filter on 0 and copy some rows to another table (that's what Call b02_articleunit0 does). If that's done I want Excel to do the same thing with filter 1 but unfortunately it's not working. Even if both filters exist, only the first one (in this case 0) will be handled. But it should also handle the second filter and save the workbook at the end with Call SaveXLS which is working fine if it can be executed.
Do you have any ideas how to optimize my code?:
Sub PivotTable()
[...]
Dim pf As PivotField
Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields("Abgleich_ME1_ME2")
'Remove existing filter
pf.ClearAllFilters
'Filter on 0
On Error GoTo fehler0:
pf.CurrentPage = "0"
Call b02_articleunit0
Exit Sub
fehler0: Worksheets("Übersicht").Range("D19").Value = "This filter isn't existing! (Filter 0)"
Resume Next
'Filter on 1
On Error GoTo fehler1:
pf.CurrentPage = "1"
Call b02_articleunit1
Exit Sub
fehler1: Worksheets("Übersicht").Range("D19").Value = "This filter isn't existing! (Filter 1)"
Resume Next
Call SaveXLS
End Sub
Edit: Because of a misunderstanding, I'll try to describe my problem better.
The Column Abgleich_ME1_ME2 could contain some values like in the table below. The columns 1, 2 and 3 describe three different cases which are possible.
1 | 2 | 3
---------------
1 | 0 | 1
1 | 0 | 0
1 | 0 | 1
1 | 0 | 1
First case: All values in the column Abgleich_ME1_ME2 are 1. The pivot table will be created with the filter as column Abgleich_ME1_ME2. Because this column contains only 1, there isn't a filter 0. So there would be an error if I execute vba code which tries to set the filter to this value. Anyway, in this case I want only the call b02_articleunit1 to be executed. Otherwise my data would be wrong.
Second case: It's the same as the first case but only for 0. At the end only the call b02_articleunit0 should be executed.
Third case: Both calls should be executed because the filter contains both values.
Aucun commentaire:
Enregistrer un commentaire