RWayneH
asked on
Need help getting a date in a Pivot Table.
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-
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Re line 6, change
"CDPSRECRPT!R1C1:R5232C36"
to
Selection.Address
Note that the lack of quotes
========
That's how I understand it. That's what the formulas that I mentioned week give you
"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
ASKER
I am still having issues adding these to the procedure... need help with this. -R-
ASKER
ASKER
I believe I need to set the pivot table range name.
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Nam e = "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.
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Nam
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)).
How do I set the range. After this I can work on the dates.
ASKER
ok... I got the range to work by using:
Now the dates..
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
Now the dates..
ASKER
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
ASKER
Thanks. -R-
Thanks for the points and glad you could figure It out while I was sleeping :-)
ASKER
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-