AppleScript and Excel 2008 PivotTable

Question

I've been having a lot trouble creating a automation script for moving Excel columns to a PivotTable. I am on OSX 10.8.5 running Excel 2008 without VB. The final goal is to convert column data fields to row data fields.

tell application "Microsoft Excel"
open file "Users:dkstaff:Desktop:new.xlsx"

tell active sheet

    tell used range
        set rc to count of rows
    end tell

    set myRange to range ("D2:D" & rc)

    set formula of myRange to "=A2&\":\"&B2&\":\"&C2"
    # select sourceData
    insert into range column 4
    set header to range "D1"
    set value of header to "KEY"
end tell

I'm clueless on how to execute a PivotTable of selected range and put it on a new worksheet.

-- tell active workbook

# new sheet
-- set new sheet with name "New Table"

# var range of data
-- set source_data to range D2:L … of sheet "data"

# var sheet
-- set PTSheet to the sheet named "pivot Table"

# Implement a new Pivot Table from source range
-- set pivot_table to make new pivot table at PTSheet with properties source_data

    tell active workbook
    set newSheet to make new worksheet at end
    tell newSheet
        set name to "Pivot Table"
    end tell
end tell

    -- Failed attempt thus far...

set source_data to range ("D2:L" & rc) of sheet "data"

set PTSheet to the sheet named "Pivot Table"

set pivot_table to make new pivot table at PTSheet with properties source_data

    ##########
-- Another failed attempt 
tell active sheet
    set source_data to range ("D2:L" & rc) of sheet "data"
    set PTSheet to the sheet named "Pivot Table"
    set pivot_table to make new pivot table at PTSheet with properties source_data
end tell

end tell

Result:error "Microsoft Excel got an error: Can’t make class pivot table." number -2710 from pivot table to class

I just can't get around this error.

My workflow within Excel would be: Select column 'key' -> PivotTable Report -> Multiple Consolidation Ranges -> I will create the page fields -> Range: data!D$2:$L$1500 -> New Sheet.

On the new sheet, double click grand total sum and be presented with the final data. There's one more step but I don't want to over complicate my workflow at this time.

If anyone has another method to automate the creation of a pivot table from an existing sheet, please mention, otherwise, I would love to continue learning AppleScript because I feel it will really help with other tasks as well.