arnoudlansdorp
New Member
- Joined
- Sep 14, 2006
- Messages
- 0
Hello,
I have created a pivottable which contains quite a large amount of data. I have protected my workbook as well, so users can't change the sheets. This means they can't use the pivottable either. If they want to filter names in a row or column in the pivottable they need to use a listbox, with muliple selections.
This is part of the code:
Num = ListFilter_issuer.ListCount
For a = 1 To Num
Variable(a) = Worksheets("Input").Cells(a + 1, 12)
With Sheets("IR Delta").PivotTables("Credit Trading Exposure").PivotFields(TextStr(a))
.PivotItems(Variable(a)).Visible = ListFilter_issuer.Selected(a- 1)
end with
next
It works, but it is rather slow. There can be a lot of names in the row and for each name it decides if it has to be visible in the pivot or not. When using the normal pivottable filter it is much, much faster. How can I use VBA, but still maintain the speed of the pivot?
Regards,
Arnoud
I have created a pivottable which contains quite a large amount of data. I have protected my workbook as well, so users can't change the sheets. This means they can't use the pivottable either. If they want to filter names in a row or column in the pivottable they need to use a listbox, with muliple selections.
This is part of the code:
Num = ListFilter_issuer.ListCount
For a = 1 To Num
Variable(a) = Worksheets("Input").Cells(a + 1, 12)
With Sheets("IR Delta").PivotTables("Credit Trading Exposure").PivotFields(TextStr(a))
.PivotItems(Variable(a)).Visible = ListFilter_issuer.Selected(a- 1)
end with
next
It works, but it is rather slow. There can be a lot of names in the row and for each name it decides if it has to be visible in the pivot or not. When using the normal pivottable filter it is much, much faster. How can I use VBA, but still maintain the speed of the pivot?
Regards,
Arnoud
