Using InputBox to select range to update Pivot

[beanhead0321]

Hey Guys,

I'm trying to update a PivotTable with the range selected in the inputbox (from another workbook), but I can't seem to get it to work. I don't know very much VBA and have just been putting pieces together so my code might be totally incoherent. Thanks in advance!

Private Sub CommandButton1_Click()
Dim SmartData As Range
     
    On Error Resume Next
    Set SmartData = Application.InputBox(prompt:="Select Data", Type:=8)
    With SmartData
        .Parent.Parent.Activate
        .Parent.Activate
        .Select
    End With
 
    
'Selects SmartData Range for update

ActiveSheet.PivotTables("PivotTable3").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        SmartData.Address, _
        Version:=xlPivotTableVersion12)
    ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
End Sub

 

[beanhead0321]

I've updated my code a little. I've got the path for the cell selection as a string, but I don't know how to have it depicted as the source for the pivot table.

Private Sub CommandButton1_Click()
Dim SmartData As Range
Dim Adres As String
     
    On Error Resume Next
    Set SmartData = Application.InputBox(prompt:="Select Data", Type:=8)
    With SmartData
        .Parent.Parent.Activate
        .Parent.Activate
        .Select
    End With
 
With ActiveSheet
    Adres = ActiveWorkbook.Path & "\[" & SmartData.Parent.Parent.Name & "]" & SmartData.Parent.Name & "!" & SmartData.Address(True, True, xlR1C1)
End With
ActiveSheet.PivotTables("PivotTable3").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Adres", _
        Version:=xlPivotTableVersion12)
    ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
End Sub