Solved

Need help getting a date in a Pivot Table.

Posted on 2014-02-11
10
398 Views
Last Modified: 2014-02-12
This was too hard to explain, so I added a comment to the code were I needed an edit.

First is Ln6, I need the pivot table to use whatever the selection area is (like UsedRange).  It cannot be the same each time.

Second there are two places and I am trying to get a proper date in a pivot table.  Please see Ln43 and Ln46 below.

Could someone help with these edits?  Please advise and thanks. -R-


Sub AddNewPivotTable()
'
    Range("A1").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "CDPSRECRPT!R1C1:R5232C36", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", TableName:="PivotTable3", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("Sheet1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Basic Matl")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields( _
        "Short Description               ")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("End Date  ")
        .Orientation = xlRowField
        .Position = 3
    End With
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("Oper. Qty"), "Sum of Oper. Qty", xlSum
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Basic Matl")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("End Date  ")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Basic Matl").CurrentPage = _
        "(All)"
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Basic Matl")
        .PivotItems("SERVICE PART").Visible = False
        .PivotItems("SERVICE PART  ").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Basic Matl"). _
        EnableMultiplePageItems = True
    ActiveSheet.PivotTables("PivotTable3").PivotFields("End Date  ").PivotFilters. _
        Add Type:=xlBefore, Value1:="3/16/2014"  'Need this to be 6 weeks out from the past Sundays date.
        
    Range("C4").Select
'this date has to be whatever last Sundays date was?

    Selection.Group Start:=41679, End:=True, Periods:=Array(False, False, False _
        , True, True, False, False)
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Chairs"
    Range("C2").Select
End Sub

Open in new window

0
Comment
Question by:RWayneH
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
10 Comments
 
LVL 43

Accepted Solution

by:
Rob earned 500 total points
ID: 39852636
On line 6, if you just want what is selected then

SourceData:=Selection.Address

Open in new window


As for determining the dates, I would use the Application.WorksheetFunction and use if the WEEKDAY function

Last Sunday is:
LastSunday=Date() - Application.WorksheetFunction.weekday(Date()) + 1

Six weeks from that is
NextDate=LastSunday + (6 * 7)
0
 

Author Comment

by:RWayneH
ID: 39852823
Not sure how to edit line 6, when I start the pivot table all I did was select cell A1 and Excel put the rest in.  Can you assist in putting the edits into the procedure?  

If I understand the dates, if today is Wednesday the 12th it should enter the date of 2-9-2014, and if today is 2-19-2014 it would enter the date 2-16-2014  correct?  -R-
0
 
LVL 43

Expert Comment

by:Rob
ID: 39852928
Re line 6, change

"CDPSRECRPT!R1C1:R5232C36"
 to
Selection.Address

Note that the lack of quotes

========

If I understand the dates, if today is Wednesday the 12th it should enter the date of 2-9-2014, and if today is 2-19-2014 it would enter the date 2-16-2014  correct?  -

That's how I understand it. That's what the formulas that I mentioned week give you
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:RWayneH
ID: 39853332
I am still having issues adding these to the procedure... need help with this. -R-
0
 

Author Comment

by:RWayneH
ID: 39853342
0
 

Author Comment

by:RWayneH
ID: 39853416
I believe I need to set the pivot table range name.


Range("A1").Select
Range(Selection, Selection.End(xlDown)).Name = "PivotTableRange"  'added to set range

then reference the name in the create.
However this just uses a column, I need to do add a horizontal part of the range.
Selection.End(xlToRight)).Select  so the xldown and xlToRight are used to set the range?

How do I set the range.  After this I can work on the dates.
0
 

Author Comment

by:RWayneH
ID: 39853437
ok... I got the range to work by using:
 Range("A1").Select
    Range(Selection, Selection.End(xlDown).End(xlToRight)).Name = "PivotTableRange"  'added to set range
    Sheets.Add.Name = "Sheet1"
    Sheets("Sheet1").Activate
    Range("A1").Select
    
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "PivotTableRange", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", TableName:="PivotTable3", DefaultVersion _
        :=xlPivotTableVersion12

Open in new window


Now the dates..
0
 

Author Comment

by:RWayneH
ID: 39853509
figured it out, all by myself. (with your help).  This is what worked.

Sub AddNewPivotTable()
'
    Range("A1").Select
    Range(Selection, Selection.End(xlDown).End(xlToRight)).Name = "PivotTableRange"  'added to set range
    Sheets.Add.Name = "Sheet1"
    Sheets("Sheet1").Activate
    Range("A1").Select
    
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "PivotTableRange", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", TableName:="PivotTable3", DefaultVersion _
        :=xlPivotTableVersion12

    Sheets("Sheet1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Basic Matl")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields( _
        "Short Description               ")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("End Date  ")
        .Orientation = xlRowField
        .Position = 3
    End With
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("Oper. Qty"), "Sum of Oper. Qty", xlSum
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Basic Matl")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("End Date  ")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Basic Matl").CurrentPage = _
        "(All)"
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Basic Matl")
        .PivotItems("SERVICE PART").Visible = False
        .PivotItems("SERVICE PART  ").Visible = False
    End With
    
    LastSunday = Date - Application.WorksheetFunction.Weekday(Date) + 1
    NextDate = LastSunday + (6 * 7)
    
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Basic Matl"). _
        EnableMultiplePageItems = True
    ActiveSheet.PivotTables("PivotTable3").PivotFields("End Date  ").PivotFilters. _
        Add Type:=xlBefore, Value1:=NextDate  'Need this to be 6 weeks out from the past Sundays date.
        
    Range("C4").Select
'this date has to be whatever last Sundays date was?
'Used LastSunday set above  this was =41679
    Selection.Group Start:=LastSunday, End:=True, Periods:=Array(False, False, False _
        , True, True, False, False)
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Chairs"
    Range("C2").Select
End Sub

Open in new window

0
 

Author Closing Comment

by:RWayneH
ID: 39853514
Thanks. -R-
0
 
LVL 43

Expert Comment

by:Rob
ID: 39854441
Thanks for the points and glad you could figure It out while I was sleeping :-)
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question