Extract data from pivot table into the table and batch print using VBA

K

kmozis

New Member
Joined
Oct 24, 2014
Messages
6
Hello,

I need an advice for this matter (all bellow mentioned is done using VBA):

- I made user form to enter data and save it to the spreadsheet in the same workbook;
- So I have spreadsheet with source data for further usage;
- From this source data I am generating various pivot tables - reports for our business;

I can do all the above and it works fine for me. Now is my task to:
- Extract data from one of the generated pivot tables;
- Put this data (every single row from the generated pivot table, not only the totals) into my own table (delivery list form which we hand over to the buyers);
- Batch print of the delivery notes (could be 30-50 rows per batch), it means at first to fill the delivery note with the data from the pivot table row 1, print it and then to do the same with the row 2, row 3,.... till the end of the pivot table.

Any ideas?
Thank you
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Welcome to MrExcel.

You can loop around the cells in the PivotTable's RowRange and ColumnRange, and use the GETPIVOTDATA function to fill out your form.
 
Upvote 0
Thank you,
I was nearly sure that the above described is possible to perform via VBA. Can you, please, provide me with sample code how to do it? I can get the data using GETPIVOTDATA from the grandtotals but not from every single record in the pivot table.
 
Upvote 0
Thank you, this is how my pivot table looks like
Pivot.jpg
 
Upvote 0
Please post your data rather than an image so that I don't have to do a lot of typing. You can copy it in Excel and paste it into a reply.
 
Upvote 0
You mean like this?:

Trasapardubice
Datum dodání24.10.2014
Expedice pokrmů dne 24.10.2014 na trase bohdaneč (vygenerováno 24.10.2014 7:45:28)
Standardní obalyOdběratelSpeciální obalyspec. obalyPolévka #1Polévka #2Hlavní #1Hlavní #2Hlavní #3Hlavní #4Hlavní #5Salát #1Salát #2Salát #3Salát #4Salát #5Salát #6Salát #7
miskyApag(Prázdné)21
BRAPO MODEL(Prázdné)7313
HANA JIRSOVÁ(Prázdné)321
Ing. František Chvojka(Prázdné)22
Install(Prázdné)72113
Silnice(Prázdné)11
SK-EKO Pardubice s.r.o.(Prázdné)13931
Celkem z misky33177561
termoportAWOS(Prázdné)332211
CEE logistics(Prázdné)25942101
STENO CZ s.r.o.(Prázdné)14842
ŠTEPANOVSKÝ - FARMA(Prázdné)981
Tapex(Prázdné)153327
T-CZ(Prázdné)874023618
Celkem z termoport183903413461
Celkový součet2161074118522
<colgroup><col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254;"> <col width="163" style="width: 122pt; mso-width-source: userset; mso-width-alt: 5961;"> <col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254;"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;" span="15"> <tbody> </tbody>

Also I forgot to mention, that the generated pivot table is of course dynamic, it means that today it has 19 rows to be extracted and printed, tomorrow it could be 30...
The structure (number of columns) is always the same.
 
Last edited:
Upvote 0
Does this work for you?

Code: 
Sub Test()
    Dim PT As PivotTable
    Dim ShTarget As Worksheet
    Dim r As Long
    Dim c As Long
    Set PT = Worksheets("denni expedice").PivotTables("DenniExpedice")
    Set ShTarget = Worksheets("dodaci list")
    With PT.RowRange.Columns(2)
        For r = 2 To .Rows.Count
            If .Cells(r, 1).Value <> "" Then
                ShTarget.Range("K6,K13:K27").ClearContents
                ShTarget.Range("K6").Value = .Cells(r, 1).Value
                ShTarget.Range("K13:K27").Value = Application.Transpose(PT.DataBodyRange.Rows(r - 1).Value)
'               Add code to print worksheet
            End If
        Next r
    End With
End Sub

I will leave you to add the Print code.
 
Upvote 0
You are great man!
I have adapted the code a little bit but generally it works perfectly for me.
Thank you very much and wish you a nice weekend!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Filter Pivot Table Generated From PowerQuery Data Model
Replies
1
Views
64
jdorby
J
I
  • Solved
Change Data source of Pivot Table from VBA
Replies
4
Views
174
ItalianPlatinum
I
M
  • Question
Excel pivot table query - retaining filters on pivot in absence of data
2
Replies
10
Views
965
Alex Blakenburg
A
sanantonio
  • Solved
Returning next Date once Date has passed
Replies
5
Views
308
dreid1011
dreid1011
S
  • Question
VBA／Click on a data field value in the Pivot table and highlight the relevant rows of source data
Replies
0
Views
233
smallxyz
S

Forum statistics

Threads
1,214,593
Messages
6,120,434
Members
448,961
Latest member
nzskater

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top