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

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