Pivot table - Macro Send to Excel does not create same spreadsheet as Icon Send to Excel
I created a pivot table showing year, customers, parts and the sales for each month (Jan, Feb, Mar....Dec)
2013 ABC Co. bolts 120 200 123 30 100 .......
nuts 120 0 300 0 103 .......
EFG Co. bolts 3 10 7 0 10 ......
The table has all the customer and part groups expanded. When I use the window icon, SEND TO EXCEL the information in the pivot table goes to excel and each line of the spread sheet is populated (there are no colums with merged cells). Just what I want.
2013 ABC Co. bolts 120 200 123 30 100 .......
2013 ABC Co. nuts 120 0 300 0 103 .......
2013 EFG Co. bolts 3 10 7 0 10 ......
I have a Macro that creates the excel spreadsheet as well. However, when I open the spreadsheet it mimics the original pivot table (the column data is grouped and shown only once).
2013 ABC Co. bolts 120 200 123 30 100 .......
nuts 120 0 300 0 103 .......
EFG Co. bolts 3 10 7 0 10 ......
Is there a VB script that works like the SEND TO EXCEL icon?
Here is the Macro Script I am using
'Create the Excel spreadsheet Set excelFile = CreateObject("Excel.Application") excelFile.Visible = true
'Create the WorkBook Set curWorkBook = excelFile.WorkBooks.Add
'Create the Sheet Set curSheet = curWorkBook.WorkSheets(1)
'Get the chart we want to export Set tableToExport = ActiveDocument.GetSheetObject("CH128")
