Pivot Table used range help

[jsabo]

Hello,

I am creating a pivot table in a new sheet in an existing workbook. I need the range to be "ActiveSheet.UsedRange" since the data changes as does the name of the sheet - these reports are automatically generated. I am getting the following error for the script below: "Run-time error '1004': This command requires at least two rows of source data". Here's the code:

[VBA]Sub CreatePivotTable()
'
' CreatePivotTable Macro
' Run this macro to add a new worksheet that contains a summary pivot table.
'
' Keyboard Shortcut: Ctrl+Shift+A
'


Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
ActiveSheet.UsedRange, Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="DSR Summary" _
, DefaultVersion:=xlPivotTableVersion14
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("DSR Summary").PivotFields("Supplier Name")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("DSR Summary").PivotFields("Agreement No")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("DSR Summary").PivotFields("Doc Status")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("DSR Summary").AddDataField ActiveSheet.PivotTables( _
"DSR Summary").PivotFields("Subcon Doc No"), "Count of Subcon Doc No", xlCount
Range("C5").Select

Cells.Replace What:="(blank)", Replacement:="Not Recvd", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1:B1").Select
With selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
selection.Merge
Range("A1:B1").Select
ActiveCell.FormulaR1C1 = "Summary"
Range("A1:B1").Select
selection.Font.Bold = True
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Summary"

End Sub[/VBA]

[jsabo]

Nevermind, got it. Did the following:

[VBA] Dim sheet As Worksheet
Set sheet = ActiveSheet

Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
sheet.UsedRange, Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="DSR Summary" _
, DefaultVersion:=xlPivotTableVersion14[/VBA]

Thanks anyways!