Retrieving Data from Pivot Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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!
 
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
'============================
 
Back
Top