Problemchen mit Update einer Pivottabelle
05.02.2018 00:40:53
Michael
ich habe mit folgendem Code ein paar Probleme und hoffe auf eure Hilfe :)
1. Ich bekomme für "usedrng = ws.Range(Cells(3, 1), Cells(lRow, lCol)).Address(ReferenceStyle:=xlR1C1)" (im Code fett markiert) folgenden Fehlermledung:
"Method range of object_Worksheet failed" - und zwar immer dann wenn ich mich nicht im Tabellenblatt ws befinde bzw. dieses aktiv ist wenn ich den Code starte. Ich habe absolut keine Ahnung warum das so ist, hat jemand eine Idee?
2. Die Pivottabelle ist so aufgebaut, dass in dem Zeilen die Geschäftsbereiche stehen und in den Spalten die Wochen (also WK1, WK2 etc.). Jetzt werden nach dem Ausführen des Codes nur diejenigen Geschäftsbereiche angezeigt (durch den Filter), die auch in WK1 vorkommen aber nicht alle, also nicht die GB, die die Wochen 2, 3 etc. betreffen.
Kann ich den Code so manipulieren, dass der Filter nach dem Update wie vor dem Update funktioniert?
3. Speichere ich die Datei, öffne sie wieder und versuche z.B. den Filter in der Pivottabelle anzuklicken, kommt folgende Fehlermeldung : " The PivottAbel report was saved without the underlying data. Use the refresh Data command to update the report"
Woran könnte das liegen, bzw. wie kann ich das unterbinden? Mein Code refreshed ja am Ende eigentlich automatisch.
4. Eine allgemeine Frage zu der ich leider noch keine gute Antwort gefunden habe: Spricht irgendwas dagegen 2 Pivottabellen in einem Tabellenblatt nebeinander anzuordnen?
besten Dank im Voraus und freundliche Grüße
Sub Pivotupdate()
Dim SrcData As String
Dim wp As Worksheet
Set wp = sheet8 ' Wieso kann ich "Worksheets(sTabPivot)" nicht mit wp ersetzen?
Dim ws As Worksheet
Set ws = Sheet10
On Error GoTo ErrorHandler
If ws.AutoFilterMode Then ws.ShowAllData ' funktioniert nicht wenn kein Filter on ist als _
_
vielleicht IF..Then fur filter on oder so
ErrorHandler:
Dim usedrng As Variant
Dim lRow As Long
Dim lCol As Long
lRow = Sheet10.UsedRange.SpecialCells(xlCellTypeLastCell).Row
lCol = Sheet10.UsedRange.SpecialCells(xlCellTypeLastCell).Column
Debug.Print (lRow)
Debug.Print (lCol)
usedrng = ws.Range(Cells(3, 1), Cells(lRow, lCol)).Address(ReferenceStyle:=xlR1C1) _
SrcData = ws.Name & "!" & usedrng
Debug.Print (usedrng)
' Const sTabPivot As String = "Pivottabellen"
With wp
.PivotTables("PivotTable5").ChangePivotCache .Parent. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
.Parent.Path & "\[" & .Parent.Name & "]" & SrcData)
.PivotTables("PivotTable9").ChangePivotCache .Parent. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
.Parent.Path & "\[" & .Parent.Name & "]" & SrcData)
.PivotTables("PivotTable5").PivotCache.Refresh
.PivotTables("PivotTable9").PivotCache.Refresh
End With
End Sub