Pivot Table to Update from other cell

Jerry Sullivan

MrExcel MVP

Joined

Mar 18, 2010

Messages

8,787

• Jan 27, 2012

• #2

Hi Mahi,

Which part of the PivotTable is your Dates field placed? (Page/Report, Row or Column fields)

Are you needing to filter for a single date or multiple dates? (From: Date1, To: Date2)

 

Upvote 0

M

maheshrk81

Board Regular

Joined

Jan 5, 2010

Messages

153

• Jan 27, 2012

• #3

Its for multiple dates.... I want to select in the main sheet and the same has to take place in the hidden pivot

 

Upvote 0

Jerry Sullivan

MrExcel MVP

Joined

Mar 18, 2010

Messages

8,787

• Jan 27, 2012

• #4

maheshrk81 said:

Its for multiple dates.... I want to select in the main sheet and the same has to take place in the hidden pivot

Click to expand...

Which part of the PivotTable is your Dates field placed? (Page/Report, Row or Column fields)

 

Upvote 0

Jerry Sullivan

MrExcel MVP

Joined

Mar 18, 2010

Messages

8,787

• Jan 27, 2012

• #5

Mahi,

You could try the VBA code at this thread. (I had thought it only worked for PageFields, but looking at it again, I believe it will work for RowFields too).

http://www.mrexcel.com/forum/showthread.php?p=3007354

I'm going offline for the night. If you have any questions on how to apply this to your purpose, I'll be glad to follow up tomorrow if another helper doesn't respond before then.

 

Upvote 0

M

maheshrk81

Board Regular

Joined

Jan 5, 2010

Messages

153

• Jan 27, 2012

• #6

Thanks a lot for your reply.
But i need the pivot should select multiple dates.
In my Sheet1, i will mention From Date and To Date in Cell(M4 and N4).
And in my control sheet, i have a pivot table(Pivottable1), Now this pivot table should change its Sales date according to Sheet1 From Date and To Date which is in Cell(M4 and N4).

Please help.

Mahi.

 

Upvote 0

Jerry Sullivan

MrExcel MVP

Joined

Mar 18, 2010

Messages

8,787

• Jan 27, 2012

• #7

Mahi, You could try the macro below. You'll need to paste this into a Standard VBA Code Module.

You'll also need to copy the Filter_PivotField_by_Date_Range function code that is shown in the thread that I referenced.

You didn't give the exact names of your Control Sheet and Sales Date field, so please edit those in the code below to match your names.

Rich (BB code):

Sub Test_Filter_Date_Range()
    Dim dtFrom As Date, dtTo As Date
    Dim PT As PivotTable
    
    With Sheets("Sheet1")
        dtFrom = .Range("M4")
        dtTo = .Range("N4")
    End With
    Set PT = Sheets("Control").PivotTables("PivotTable1")
    
    Call Filter_PivotField_by_Date_Range( _
        PT.PivotFields("Sales Date"), dtFrom, dtTo)
End Sub

 

Upvote 0

M

maheshrk81

Board Regular

Joined

Jan 5, 2010

Messages

153

• Jan 30, 2012

• #8

Thanks for the reply sir...
But i am getting the "Run Time error 1004" - Unable to get the pivot fileds property of the pivot table class".

I have my dates in Sheet1 and the Pivot is in "control" sheet with the piovt table name "PivotTable1" and the files name as "Sales date".

Please help.

Thanks a lot.
Mahi

 

Upvote 0

Jerry Sullivan

MrExcel MVP

Joined

Mar 18, 2010

Messages

8,787

• Jan 30, 2012

• #9

Hi Mahi,

If you will turn the macro recorder on and do the steps manually, it will help in diagnosing the problem with the code.

To do this manually, go to your PivotTable and click on the filters for the date field to show the checkboxes for each item. Uncheck all items, then just check 2 items.

Stop the recorder and post the code that it has recorded.

 

Upvote 0

M

maheshrk81

Board Regular

Joined

Jan 5, 2010

Messages

153

• Jan 30, 2012

• #10

Thanks a lot jerry, now the macro is working fine. But it is not solving my problem.
I want the dates to be selected from 7/1/2010 to 1/26/2011, but when i run the macro it just poping up the below msg.
"No items are within the specified dates."

Please help me..

Thanks Mahi

 

Upvote 0