Using VBA to copy a pivottable

[David31101990]

Hello,

I wanted to use a VBA function that is able to copy a pivottable which is on a server into my workbook. This is the VBA i'm using now.

Sub CopyPvts()
Dim ws As Worksheet
Dim wb As Workbook
Dim SheetList
Dim i As Long
SheetList = Array("A", "B") ' change to suit - these are the sheet names to copy TO and must exist
Set wb = Workbooks.Open("C:\Users\David\Documents\Afstudeerstage\DraaiTabelGebruikAfprijzing2007.xlsx") ' change to suit
For Each ws In Worksheets(Array("AfprPerFilPerSubgrpPerArtGELD", "AfprPerFilPerSubgrpPerArtCE")) ' change to suit = sheet names to copy FROM
    ThisWorkbook.Sheets(SheetList(i)).ClearContents
    ws.UsedRange.Copy Destination:=ThisWorkbook.Sheets(SheetList(i)).Range("A1")
    i = i + 1
Next ws
wb.Close savechanges:=False
End Sub

When I run the macro, I get "Run-time error '438': Object doesn't support this property or method". When I click 'Debug', "ThisWorkbook.Sheets(SheetList(i)).ClearContents" is highligted. What am I doing wrong? I changed the names to suit so that isn't the problem.

Thanks

David