For Next loop on Pivot Table field

[Hermac]

Hello,

I wrote this code to repeatedly copy part of a pivot table in a workbook (source) end valuepaste it in another workbook .(target).
Then go back to the pivot table where a field called "Groupe" should itterate from A to S. For those letters I use the string variable "Groep"
I get stuck all the time on the 23rd line (blue line) of my code. Can someone help me out here?
Thank you very very much
Herman


Dim i As Integer, j As Integer, k As Integer, c As Range, R As Range

Dim Mes As String, Regio As String, Groep As String

Dim PerNr As Range





Sub CopyToCollector()

Dim WbBron As Workbook, WbDoel As Workbook

Dim WsBron As Worksheet, WsDoel As Worksheet, Bronvlak As Range

Set WbBron = ActiveWorkbook: Set WsBron = ActiveSheet

If MsgBox("Staat er een DOELBOEK open ?", vbYesNo) = vbNo Then

Mes = Application.InputBox("Hoe moet het Doelboek heten?", Type:=2)

Workbooks.Add.SaveAs Filename:=Mes

Set WbDoel = ActiveWorkbook: Set WsDoel = ActiveSheet: Set R = [C2]

Else

Set R = Application.InputBox(prompt:="KLIK ERGENS op de EERSTVOLGENDE DOELCEL op het DOELBLAD in het DOELBOEK", Type:=8)

Set WsDoel = R.Parent: Set WbDoel = R.Parent.Parent

End If

WbBron.Activate: WsBron.Activate

Regio = Application.InputBox("Welke Regio heb je geselecteerd?", Type:=2)

ActiveSheet.PivotTables("Draaitabel1").PivotFields("Groep/groupe").CurrentPage = "(All)"

ActiveSheet.[C1:C300].Find("Totaal Werknemers").Resize(3, 20).Copy

WbDoel.Activate: WsDoel.Activate: R.PasteSpecial xlPasteValues

R.Offset(0, -2).Resize(3, 1) = Regio

R.Offset(0, -1).Resize(3, 1) = "Alle"

Set R = R.Offset(3, 0)

WbBron.Activate: WsBron.Activate

For i = 65 To 83

Groep = Chr(i)

ActiveSheet.PivotTables("Draaitabel1").PivotFields("Groep/groupe").CurrentPage = Groep

ActiveSheet.[C1:C300].Find("Totaal Werknemers").Resize(3, 20).Copy

WbDoel.Activate: WsDoel.Activate: R.PasteSpecial xlPasteValues

R.Offset(0, -2).Resize(3, 1) = Regio

R.Offset(0, -1).Resize(3, 1) = Groep

Set R = R.Offset(3, 0)

Next i

End Sub

 

[Hermac]

My problem is solved. I had just been stupid. Please disregard my post of yesterday

 

[Alex Blakenburg]

It was certainly working fine for me.