[Leon M]

Szanowni Forumowicze,

Zwracam się z wielką prośbą o napisanie makra, które tworzyć będzie 2 tabele przestawne, dla których źródłem danych będzie dynamiczny zakres arkuszowy (zmienna ilość wierszy w zależności od ilości danych).
Założenie jest takie, że po zmianie danych w tym zakresie wywołanie procedury spowoduje utworzenie na nowo tabeli przestawnej obejmującej nową zawartość danych w zakresie źródłowym.

W załączeniu zamieszczam plik z zawartością, na której przewidziane jest działanie makra.
W arkuszu "Produkt" znajduje się krótki opis zadania.

Za odpowiedzi z góry serdecznie dziękuję.

---

Tabela przestawna w VBA.xlsm

Pobierz Plik ściągnięto 28 raz(y) 91.12 KB

[Tajan]

A czy konieczne jest tworzenie od podstaw tabeli przestawnej? Nie wystarczy uaktualnienie zakresu źródła danych dla istniejącej tabeli przestawnej?
Przykładowo, dodaj na końcu procedury "WyszukajProdukt":

Kod:

With Sheets("Analiza1").PivotTables(1)         .ChangePivotCache _         ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _         SourceData:=Range("D5:F" & ostW), _         Version:=xlPivotTableVersion15)         .RefreshTable End With

co po zmianie zawartości arkusza "Produkty" spowoduje odświeżenie i uaktualnienie tabeli przestawnej w arkuszu "Analiza1".

[Leon M]

Tajan, bardzo dziękuję za odpowiedź.

Tak, wystarczającą będzie możliwość odświeżenia istniejących tabel. Nie pomyślałem o takim sposobie rozwiązania zadania.

Chciałbym zapytać, czy PivotTables(1) to jest pierwsza tabela przestawna w arkuszu, czy w całym skoroszycie. Ponadto, czy można tabelę przestawną opatrzyć indywidualną nazwą i jeśli tak, to jak rzecz wykonać?

[Tajan]

Obiekt "PivotTables" odnosi się do arkusza. Zamiast poprzez indeks, czyli "Sheets("Analiza1").PivotTables(1)", możesz do tabeli odwoływać się poprzez jej nazwę, np. "Sheets("Analiza1").PivotTables("MojaTabela"), przy czym jej nazwę zmienisz w menu "Analiza tabeli przestawnej", opcja: "Tabela przestawna", jak widać to w załączonym obrazie.

---

NazwaTabeli.png

Plik ściągnięto 16 raz(y) 52.65 KB

[Leon M]

Tajan, bardzo dziękuję za przedstawione wyjaśnienia.

[Leon M]

Szanowni Forumowicze,

Chciałbym dopytać odnośnie kodu przedstawionego powyżej przez kolegę Tajana.

Otóż, kod ten dotyczył przypadku, gdy źródłem danych dla odświeżanej tabeli przestawnej był zwykły zakres arkuszowy.
Chciałbym poprosić o przedstawienie, jaka byłaby składnia tej części kodu, która odnosi się do wskazania źródła danych, w przypadku, gdy źródłem tym byłaby tabela.

Za odpowiedzi z góry bardzo dziękuję.

[Tajan]

Po prostu jako źródło danych wskaż daną tabelę. Czyli w kodzie:

Kod:

With Sheets("Analiza1").PivotTables(1)         .ChangePivotCache _         ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _         SourceData:=Range("D5:F" & ostW), _         Version:=xlPivotTableVersion15)         .RefreshTable End With

fragment:

Kod:

SourceData:=Range("D5:F" & ostW)

zamień na odwołanie do tabeli, np.:

Kod:

SourceData:=ActiveSheet.ListObjects(1)

czy

Kod:

SourceData:=ActiveSheet.ListObjects("Tabela1")

[Leon M]

Tajan, bardzo dziękuję za wyjaśnienia.

[Leon M]

Chciałbym jeszcze poprosić o wyjaśnienie znaczenia poniższej linii kodu:

Kod:

Version:=xlPivotTableVersion15)

Za odpowiedzi z góry bardzo dziękuję.

[Leon M]

Pojawiła się jeszcze jedna kwestia, względem której chciałbym zadać pytanie.

Otóż, jak można pozyskać wartość, która jest liczbą tabeli przestawnych znajdujących się w danym arkuszu?

[ple4]

[Kaper]

PivotTables w danym arkuszu to kolekcja, więc zadziała metoda Count
np. sprawdź wynik

Kod:

msgbox activesheet.pivottables.count

[Leon M]

ple4, Kaper,

Bardzo dziękuję za kod i wyjaśnienia.

Wyłoniła się kolejna kwestia związana z odświeżaniem tabeli przestawnej, z którą nieporadnie się borykam.
Otóż chodzi o formatowanie warunkowe tabeli.
W przypadku, gdy zmiana danych źródłowych powoduje po odświeżeniu dodanie wierszy w tabeli przestawnej, powstałe w ten sposób nowe pola nie otrzymują formatowania warunkowego, jakie mają komórki w danej kolumnie tabeli.

Zatem moje pytanie jest takie: jak w VBA nadać nowo powstałym polom formatowanie warunkowe, jakim były sformatowane poszczególne kolumny przed odświeżeniem?

Za odpowiedzi bardzo dziękuję.

[ple4]

... ...
Tabela przestawna, tak jak i obiekt tabeli ('ListObject') w jednym z twoich innych tematów, również ma właściwość "DataBodyRange" - zastosuj ją po odświeżeniu danych - coś a'la:

Kod:

Sub pomidorowa_z_grochem()     Dim sh As Worksheet     Dim pvttbl As PivotTable     Dim okeyshadoky As Boolean         Set sh = ThisWorkbook.Worksheets("Analiza1")     Set pvttbl = sh.PivotTables(1)         okeyshadoky = pvttbl.RefreshTable     If Not okeyshadoky Then Exit Sub         With pvttbl.DataBodyRange         ' ... Formatowanie warunkowe, inne, etc.     End With End Sub

[Leon M]

ple4,

Bardzo dziękuję za kod i wyjaśnienia.

Chciałbym zapytać, czy poniższe linie kodu:

Kod:

okeyshadoky = pvttbl.RefreshTable If Not okeyshadoky Then Exit Sub

Mają za zadanie sprawdzić, czy miało miejsce odświeżenie tablicy?