Im so close. Need variable range for the pivot table

[Loren]

Excel macro as recorded. "Sheet1!R4C3:R50C5" needs to be made variable
when the input file changes,. thank you
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/5/2004 by lander11
'
ActiveWindow.LargeScroll ToRight:=1
Range("L:L,S:S,U:U").Select
Range("U1").Activate
Selection.Copy
Workbooks.Add Template:="Workbook"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("B5").Select
Application.CutCopyMode = False
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Range("C4").Select
ActiveCell.FormulaR1C1 = "mon"
Range("C5").Select
ActiveCell.FormulaR1C1 = "=MONTH(RC[-2])"
Range("C5").Select
Selection.AutoFill Destination:=Range("C5:C50")
Range("C5:C50").Select
Range("C5").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _
"Sheet1!R4C3:R50C5").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1"
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields
RowFields:="CUST_CONC_CD", _
ColumnFields:="mon"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("mon")
.Orientation = xlDataField
.Caption = "Count of mon"
.Function = xlCount
End With
End Sub

 

[Tom Ogilvy]

SourceData:= _
"Sheet1!R4C3:R50C5"

might be

Sourceata= _
Worksheets("sheet1").Range("C4").CurrentRegion _
.Address(1,1,xlR1C1,True)).CreatePivotTable TableDestination:="",

It depends on if your database has its upper left corner in C4.