Copy Pivot table and pasting in Excel

[MEJ]

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).

[MEJ]

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.

[MEJ]

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.

[andregroen]

Hi Mathias,

For a regular export, this may help. Hopefully this may help!

1. Make an Excel template (example attached)
2. Make a table with country, product & sum(Revenue) (check the chartcode!)
3. Make a macro (see below) 
4. 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