Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table variable range
I have 1 sheet where timesheet data is add.
a 2nd sheet contains a pivot table with dates of sheet1 (timesheet details) grouped by qtrs and months. As the time sheet entries are added I need to alter the pivot table range in sheet 2 to suit as if there are any blank dates (even if not selected)then grouping doesn't work (therefore can't just use R3C1:R35000C14) allowing for future time sheet entries. I want to creat a command button on sheet 1 so that after input of new timesheet data on pressing the command button the last row of data is indentified and then the pivot table range is adjusted and refreshed. I have the code working to find the last row no. but keep getting an error when trying to update the pivot table range. I show code below :- Sub LastRowWithoutFormula() Dim NoF, i, r As Integer Range("A65536").End(xlUp).Select NoF = Selection.Row Range(NoF & ":" & NoF).Select For i = 10 To 1 Step -1 For Each Cell In Selection If Cell.HasFormula Then NoF = NoF - 1 Range(NoF & ":" & NoF).Select Else Exit For End If Next Next i r = ActiveCell.Row Cells(r, 14).Select Sheets("Sheet1").Select Range("C6").Select ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Time Sheet Information '!R3C1:R("r")C14" **** line which won't work ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table variable range
You could use a dynamic source for the pivot table, and it will adjust
automatically as new rows are added. There are instructions he http://www.contextures.com/xlPivot01.html La Durande wrote: I have 1 sheet where timesheet data is add. a 2nd sheet contains a pivot table with dates of sheet1 (timesheet details) grouped by qtrs and months. As the time sheet entries are added I need to alter the pivot table range in sheet 2 to suit as if there are any blank dates (even if not selected)then grouping doesn't work (therefore can't just use R3C1:R35000C14) allowing for future time sheet entries. I want to creat a command button on sheet 1 so that after input of new timesheet data on pressing the command button the last row of data is indentified and then the pivot table range is adjusted and refreshed. I have the code working to find the last row no. but keep getting an error when trying to update the pivot table range. I show code below :- Sub LastRowWithoutFormula() Dim NoF, i, r As Integer Range("A65536").End(xlUp).Select NoF = Selection.Row Range(NoF & ":" & NoF).Select For i = 10 To 1 Step -1 For Each Cell In Selection If Cell.HasFormula Then NoF = NoF - 1 Range(NoF & ":" & NoF).Select Else Exit For End If Next Next i r = ActiveCell.Row Cells(r, 14).Select Sheets("Sheet1").Select Range("C6").Select ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Time Sheet Information '!R3C1:R("r")C14" **** line which won't work ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh End Sub -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Table - Variable Range | About this forum | |||
Entering Variable in a Pivot Table | Excel Discussion (Misc queries) | |||
Pivot table with a variable range in the sourcedata field | Excel Discussion (Misc queries) | |||
Im so close. Need variable range for the pivot table | Excel Programming | |||
Pivot Table - Object variable or with block variable not set? | Excel Programming |