How to use DataBodyRange property to write to a specific sheet?

[snkartha]

Hi,

I am new to VBA programming. I got this code snippet and it reads a pivot table and it CREATES A NEW WORKSHEET in the workbook and then writes the content to it. I want to know how I can modify this code to write to a specific sheet in the same workbook. Like for example, I want the contents to write to a specific sheet "Results_Obtained" in the same workbook. Is there any option ? Here is the code snippet.

 
For Each pvt In ActiveSheet.PivotTables
    With pvt
        .ColumnGrand = True
        .RowGrand = True
    End With
    Set rng = pvt.DataBodyRange
    rng.Cells(rng.Rows.Count, rng.Columns.Count).ShowDetail = True
Next pvt

My Pivot Table looks like the one I have put in the attachment (vbacodehelp.xls). It is in the sheet PivotTable.




From the code snippet I have provided above, the output I get perfectly is as shown in the attachement (Sheet from code snippet).

The only problem is, the code writes to a new sheet, rather than a sheet which I want to. Can someone help. Thanks

Edit: Vba tags added to code. Select your code when posting and hit the vba button to format it for the forum.