
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Copy Pivot table and pasting in Excel
Hi all,
Have been using this excellent forum for inspiration for many years. Most of the times I can find a solution here, otherwise I have to invent something myself.
I have a problem that I can't seem to find a solution for. I am trying to copy a Pivot table in Qlikview and pasting it into Excel (version 2013). I can either right click on the table and sending it to Excel. In this case it keeps all number formats (thousand separators and percentages). However it does not keep the Pivot dimensions as it looks in the Qlikview table.
The other option I can do, is to copy to clipboard (Full table). This gives me the correct dimension (merged cells), but the thousand separators are gone, which means I have to either fix it in Excel (merge the dimension), which can be very time consuming, depending on the number of dimensions, and sub dimensions.
The best solution I have found is so far, is to send to Excel, and then copy to clipboard and copy the merged dimensions into the first table.
As I send out numerous reports daily (approximately 30), I would save a lot of time by being able to get it in the correct format at once).

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Am I the only one having this problem? It causes a lot of grief as I have to spend a bit too much time fiddling around in Excel with merging Pivot cells.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I found a way to at least save half the time. Not perfect, but much better than before.
First I use "Send to Excel", which opens up the output in Excel (although not with merged cells).
After that I go back to the Pivot Table in Qlikview, and right click on the report and Copy Full table to Clipboard.
I then go into Excel and paste Special Match Destination formatting (M).
It is good enough for now, but ideally I would like to copy Full table to Clipboard and pasting it in Exactly the format in Qlikview Pivot Table without having to do a two step process. It would save me a lot of time each day.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Mathias,
For a regular export, this may help. Hopefully this may help!
- Make an Excel template (example attached)
- Make a table with country, product & sum(Revenue) (check the chartcode!)
- Make a macro (see below)
- Ad a button with macro
Sub ExportPivotToExcel
strPathExcel = "C:\Users\Qlik\Documents\Report\" 'change path
FileName = "RevenueTemplate.xlsx" 'the template, this document needs the desired layout
SET XL_Export = CreateObject("Excel.Application")
XL_Export.Visible = True
XL_Export.Workbooks.Open strPathExcel&FileName
'Export revenue
set objSheet = XL_Export.WorkSheets("Data")
objSheet.Rows("3:1000000").clearContents() 'delete current data, except for first row (otherwise an excel error occurs)
ActiveDocument.GetSheetObject("CH00").CopyTableToClipboard False 'The chartcode of step 2
objSheet.Paste objSheet.Range("A2")
'Save document
Datum = Date
strPathExcel2 = "C:\Users\Qlik\Documents\Report\" 'change
FileName2 = Datum & "_RevenuePerCountryPerProductcode.xlsx"
XL_Export.ActiveWorkbook.SaveAs strPathExcel2 & FileName2
XL_Export.Quit
XL_Export.Visible = True
End Sub
