After I run this macro how do I activate lastcol2? That is, row 2 of the last column. I tried "Range(Cells(lastcol + 1), 2).Activate" but that did not work.
VBA Code:
'Multiple Rate Check
Sheets("Combined Time").Select
Dim rng2 As Range
Dim sht2 As Worksheet
Dim pTable2 As PivotTable
Set rng2 = ActiveSheet.Cells(1, 1).CurrentRegion
Set sht2 = ActiveWorkbook.Worksheets.Add
sht2.Name = "Rate Check Pivot"
Set pTable2 = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
rng2.Address, Version:=8).CreatePivotTable(TableDestination:= _
sht2.Cells(1, 1), TableName:="PivotTable" & Format(Time, "hhmmss"))
With pTable2
With .PivotFields("SSN")
.Orientation = xlRowField
.Subtotals(1) = False
End With
With .PivotFields("Temp Rate")
.Orientation = xlColumnField
.Subtotals(1) = False
End With
.PivotFields("Reg Hours").Orientation = xlDataField
End With
Range("B3").Select
Dim lastcol As Long
lastcol = Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column
Cells(ActiveCell.Row, lastcol + 1).Select
ActiveCell.Value = "=COUNT(RC[" & (1 - lastcol) & "]:RC[-2])"
With ActiveCell
Range(Cells(3, lastcol + 1), Cells(Range("A" & Rows.Count).End(xlUp).Row - 1, lastcol + 1)).FillDown
End With