Retrieving Data from Pivot Table

To retrieve data from a pivot table the moment this table is changed I use
the code shown beneath. I thought it should work without problems, but it
does not. I really can't seem to figure this problem out!

I hope you can help me! Thanks in advance!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$20" Then
Sheets("INFO Dbase Uren").PivotTables("INFO Dbase Uren") _
.PivotFields("ARF No.").CurrentPage = Target.Value
Sheets("INFO Dbase Uren").Select
ActiveWindow.SmallScroll Down:=500
Range("A500").Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.Offset(0, 200)).Select
Selection.Copy
Sheets.Add.Name = "TEMP"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A20000").Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.Offset(0, 200)).Select
Sheets("DBase Organic Planning").Select
Range("A1").Select
Selection.End(xlDown).Offset(3, 9).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End If
End Sub

You need to qualify your references, otherwise they're assumed to refer
to the sheet that contains the event code. For example,

Sheets("INFO Dbase Uren").Select
ActiveWindow.SmallScroll Down:=500
Range("A500").Select

should be:

Sheets("INFO Dbase Uren").Select
Sheets("INFO Dbase Uren").Range("A500").Select

Ideally, you could modify the code to work without selecting.

Jasper wrote:
> To retrieve data from a pivot table the moment this table is changed I use
> the code shown beneath. I thought it should work without problems, but it
> does not. I really can't seem to figure this problem out!
>
> I hope you can help me! Thanks in advance!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Address = "$D$20" Then
> Sheets("INFO Dbase Uren").PivotTables("INFO Dbase Uren") _
> .PivotFields("ARF No.").CurrentPage = Target.Value
> Sheets("INFO Dbase Uren").Select
> ActiveWindow.SmallScroll Down:=500
> Range("A500").Select
> Selection.End(xlUp).Select
> Range(Selection, Selection.End(xlUp)).Select
> Range(Selection, Selection.Offset(0, 200)).Select
> Selection.Copy
> Sheets.Add.Name = "TEMP"
> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
> False, Transpose:=False
> Rows("1:1").Select
> Selection.Delete Shift:=xlUp
> Range("A20000").Select
> Selection.End(xlUp).Select
> Range(Selection, Selection.End(xlUp)).Select
> Range(Selection, Selection.Offset(0, 200)).Select
> Sheets("DBase Organic Planning").Select
> Range("A1").Select
> Selection.End(xlDown).Offset(3, 9).Select
> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
> False, Transpose:=False
> End If
> End Sub
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks! You mentioned "I could modify the code to work without selecting",
this looks like a way better option I'm working on right now. Could you show
me how I could do such a thing?

Thanks in Advance!

"Debra Dalgleish" wrote:

> You need to qualify your references, otherwise they're assumed to refer
> to the sheet that contains the event code. For example,
>
> Sheets("INFO Dbase Uren").Select
> ActiveWindow.SmallScroll Down:=500
> Range("A500").Select
>
> should be:
>
> Sheets("INFO Dbase Uren").Select
> Sheets("INFO Dbase Uren").Range("A500").Select
>
> Ideally, you could modify the code to work without selecting.
>
> Jasper wrote:
> > To retrieve data from a pivot table the moment this table is changed I use
> > the code shown beneath. I thought it should work without problems, but it
> > does not. I really can't seem to figure this problem out!
> >
> > I hope you can help me! Thanks in advance!
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Address = "$D$20" Then
> > Sheets("INFO Dbase Uren").PivotTables("INFO Dbase Uren") _
> > .PivotFields("ARF No.").CurrentPage = Target.Value
> > Sheets("INFO Dbase Uren").Select
> > ActiveWindow.SmallScroll Down:=500
> > Range("A500").Select
> > Selection.End(xlUp).Select
> > Range(Selection, Selection.End(xlUp)).Select
> > Range(Selection, Selection.Offset(0, 200)).Select
> > Selection.Copy
> > Sheets.Add.Name = "TEMP"
> > Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
> > False, Transpose:=False
> > Rows("1:1").Select
> > Selection.Delete Shift:=xlUp
> > Range("A20000").Select
> > Selection.End(xlUp).Select
> > Range(Selection, Selection.End(xlUp)).Select
> > Range(Selection, Selection.Offset(0, 200)).Select
> > Sheets("DBase Organic Planning").Select
> > Range("A1").Select
> > Selection.End(xlDown).Offset(3, 9).Select
> > Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
> > False, Transpose:=False
> > End If
> > End Sub
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

Using the macro recorder can help you learn about the Excel object
model, but it doesn't create the most efficient code. Usually you can
edit the resulting code, to make it work faster. For example, if you
want to copy a pivot table, and copy the values to a new sheet, the
start of the code could be changed to:

'=============================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsInfoDb As Worksheet
Dim wsNew As Worksheet
Dim ptInfoDb As PivotTable
Dim pfARF As PivotField
Set wsInfoDb = Sheets("INFO Dbase Uren")
Set ptInfoDb = wsInfoDb.PivotTables("INFO Dbase Uren")
Set pfARF = ptInfoDb.PivotFields("ARF No.")

If Target.Address = "$D$20" Then
pfARF.CurrentPage = Target.Value
Set wsNew = Worksheets.Add
wsNew.Name = "TEMP"
'use TableRange2 if you want to include page fields
ptInfoDb.TableRange1.Copy
wsNew.Range("A1").PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'============================

Jasper wrote:
> Thanks! You mentioned "I could modify the code to work without selecting",
> this looks like a way better option I'm working on right now. Could you show
> me how I could do such a thing?
>
> Thanks in Advance!
>
> "Debra Dalgleish" wrote:
>
>
>>You need to qualify your references, otherwise they're assumed to refer
>>to the sheet that contains the event code. For example,
>>
>> Sheets("INFO Dbase Uren").Select
>> ActiveWindow.SmallScroll Down:=500
>> Range("A500").Select
>>
>>should be:
>>
>> Sheets("INFO Dbase Uren").Select
>> Sheets("INFO Dbase Uren").Range("A500").Select
>>
>>Ideally, you could modify the code to work without selecting.
>>
>>Jasper wrote:
>>
>>>To retrieve data from a pivot table the moment this table is changed I use
>>>the code shown beneath. I thought it should work without problems, but it
>>>does not. I really can't seem to figure this problem out!
>>>
>>>I hope you can help me! Thanks in advance!
>>>
>>>Private Sub Worksheet_Change(ByVal Target As Range)
>>> If Target.Address = "$D$20" Then
>>> Sheets("INFO Dbase Uren").PivotTables("INFO Dbase Uren") _
>>> .PivotFields("ARF No.").CurrentPage = Target.Value
>>> Sheets("INFO Dbase Uren").Select
>>> ActiveWindow.SmallScroll Down:=500
>>> Range("A500").Select
>>> Selection.End(xlUp).Select
>>> Range(Selection, Selection.End(xlUp)).Select
>>> Range(Selection, Selection.Offset(0, 200)).Select
>>> Selection.Copy
>>> Sheets.Add.Name = "TEMP"
>>> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
>>> False, Transpose:=False
>>> Rows("1:1").Select
>>> Selection.Delete Shift:=xlUp
>>> Range("A20000").Select
>>> Selection.End(xlUp).Select
>>> Range(Selection, Selection.End(xlUp)).Select
>>> Range(Selection, Selection.Offset(0, 200)).Select
>>> Sheets("DBase Organic Planning").Select
>>> Range("A1").Select
>>> Selection.End(xlDown).Offset(3, 9).Select
>>> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
>>> False, Transpose:=False
>>> End If
>>>End Sub
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html