Hello.
I'm rather new to doing macros. I have a little programming experience from college. I have a speadsheet that I'm trying to create a macro for that needs a dynamic range. My header line is on Row 5. Currently when I run my code it appears that I'm getting an extra row at the bottom that is all blank so when I view my pivot table I end up with a blank fiscal year column and blank fiscal qtr rows. Because of this I believe it is causing my second issue where I cannot group by month on the invoice date section.
I've been working on this for a couple of days and I'm very frustrated. I appreciate any help you can provide.
Code section:
ActiveWorkbook.Names.Add Name:="PvtData", RefersToR1C1:= _
"=OFFSET('Sell thru Invoice Detail for Ma'!R5C1,0,0,COUNTA('Sell thru Invoice Detail for Ma'!C1),COUNTA('Sell thru Invoice Detail for Ma'!R5))"
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"PvtData", Version:= _
xlPivotTableVersion10).CreatePivotTable TableDestination:="Sheet1!R3C1", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Fiscal year")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Fiscal qtr")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Extended Cost"), "Sum of Extended Cost", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Invoice " & Chr(10) & "Date")
.Orientation = xlRowField
.Position = 2
End With
Range("B4").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
False, True, False, False)
Bookmarks