Code for prompts for user to select datasource range for pivot tab

[Linda]

Hi All,

Below I copied part of a macro to help illustrate my question. In the data
file I use dialog boxes that prompt the user to select the ranges...these
work perfectly. However, I need to do the same for the SourceData when
creating a pivot table. I have just entered a range in this example below.

The range cannot be named, because a new file is extracted each time, the
number of rows change and is unknown, and the file is more than 36 columns
wide (max for pivot tables).

Any help with how to allow the user to select the SourceData range for the
pivot will be greatly apprecicated.

THANKS!
Linda

Dim UserRange As Range
Prompt = "Select a Range starting at Columns B-L, Rows 3-last row in
Columns B-L"
Title = "Select Range"
' Display the Input Box
On Error Resume Next
Set UserRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Type:=8) 'Range selection
'Default:=ActiveCell.Address,
' Was the Input Box canceled?
If UserRange Is Nothing Then
MsgBox "Canceled."
Else
Range("B2:L2").Select
Selection.Copy
UserRange.Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Range("B2").Select
Prompt = "Select a Copy Range all rows Column B only"
Title = "Select Copy Range"
' Display the Input Box
On Error Resume Next
Set UserRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Type:=8) 'Range selection
'Default:=ActiveCell.Address,
' Was the Input Box canceled?
If UserRange Is Nothing Then
MsgBox "Canceled."
Else
UserRange.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Selection.AutoFilter
Cells.Select
Cells.EntireColumn.AutoFit
Columns("B:I").Select
Selection.EntireColumn.Hidden = True
Range("A2").Select
ActiveSheet.Name = "Current Oblig"
End If
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Current Oblig'!R1C1:R5924C36").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:=Array("BU
Code", _
"Vendor Alpha", "VENDOR_NAME", "FY", "PO_NUMBER")
With
ActiveSheet.PivotTables("PivotTable1").PivotFields("OBLIGATION_AMOUNT")
.Orientation = xlDataField
.Caption = "Sum of OBLIGATION_AMOUNT"
.Function = xlSum
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("FY").Subtotals =
Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)

ActiveSheet.PivotTables("PivotTable1").PivotFields("VENDOR_NAME").Subtotals =
_
Array(False, False, False, False, False, False, False, False, False,
False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Vendor
Alpha").Subtotals = _
Array(False, False, False, False, False, False, False, False, False,
False, False, False)
Columns("F:F").Select
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Range("A1").Select
ActiveCell.FormulaR1C1 = "All Obligations"
Selection.Font.Bold = True