Microsoft Excel

Herbers Excel/VBA-Archiv

Problemchen mit Update einer Pivottabelle


Betrifft: Problemchen mit Update einer Pivottabelle von: Michael Kling
Geschrieben am: 05.02.2018 00:40:53

Hallo,

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

  

Betrifft: Teillösung 2/4 von: ChrisL
Geschrieben am: 05.02.2018 11:20:57

Hi Michael

1.
ws.Range(ws.Cells(3, 1), ws.Cells(lRow, lCol)).Address(ReferenceStyle:=xlR1C1)

4.
M.E. nein

cu
Chris


  

Betrifft: AW: Teillösung 2/4 von: Michael Kling
Geschrieben am: 06.02.2018 05:20:49

Servus Chris, vielen Dank! Auf 1. hätte ich echt selbst kommen müssen, fail :D