Assign a variable to an object parameter

I need to create a pivot table with vba. Using the macro recorder I understand the statement is:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"Sheet1!R1C1:R398C6", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="Tabella_pivot1", _
DefaultVersion:=xlPivotTableVersion14

The SourceData parameter specifies where the input data are. In the above case data are on sheet1 and the data range is row 1 col 1 to row 398 to col 6.
I need to change this value according to what the user store in Sheet1. For this reason I have modified the above code in this way:


ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"Sheet1!R1C1:R" & Mid(Str(UR), 2) & "C6", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Pivot!R3C1", TableName:="Tabella_pivot1", _
DefaultVersion:=xlPivotTableVersion14

where UR is variable declared as Integer containing the last row range.

Unfortunately, I get a run time error 5.

Please, can somebody tell me where I am wrong? Thanks

This works fine for me

    ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:="Sheet1!R1C1:R" & UR & "C6", _
    Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Pivot!R3C1", _
        TableName:="Tabella_pivot1", _
        DefaultVersion:=xlPivotTableVersion14

I apologize. English is not my native language. I meant UR contains the number of the last row of the range representing the datasource for the pivot table.

I tried with the source by Bob but it doesn't work. I am using Excel 2010.

Looking at the debug it really contains a number, but still I get a runtime error 5.

No. Some code executed before the pivot creation delete the sheet (if existing) used to contain the pivot table. In any case I am debugging and I have checked the pivot table does not exist.

Yes, Thank you. As you can see data come from access, but I think this is not important.

Private Sub CommandButton1_Click()
 
 Dim cn         As ADODB.Connection
 Dim rts        As New ADODB.Recordset
 Dim UR         As Integer
 Dim pollo      As String
  
 Set cn = New ADODB.Connection

 ' Evita il lampeggio del video in esecuzione e modifica l'aspetto del cursore
 ' Application.ScreenUpdating = False
 ' Application.Cursor = xlWait
 ' Application.DisplayAlerts = True


 ' Se il foglio SITCON esiste già ne pulisce il contenuto altrimenti lo crea nella subroutine Errore_Foglio_Sitcon
  Foglio = 1
  On Error GoTo Errore_Foglio
  Worksheets("Sitcon").Select
  Cells.Select
  Selection.ClearContents

 ' Apre connessione al database ed esegue la query
  cn.Open "Provider=Microsoft.Ace.OLEDB.12.0; " & _
          "Data Source=c:\Analisi Bilancio\Bilancio.accdb"
        
  rts.Open "SELECT Sitcon.[Periodo], " _
     & "Sitcon.[Mastro] & chr(32) & chr(45) & chr(32) &(SELECT Pdcs.[Descrizione] FROM Pdcs where Pdcs.[Conto] = Sitcon.[Mastro]) AS Mastro, " _
     & "Sitcon.[Intermedio] & chr(32) & chr(45) & chr(32) & (SELECT Pdcs.[Descrizione] FROM Pdcs where Pdcs.[Conto] = Sitcon.[Intermedio]) AS Intermedio, " _
     & "Sitcon.[Codice] & chr(32) & chr(45) & chr(32) & (SELECT Pdcs.[Descrizione] FROM Pdcs where Pdcs.[Conto] = Sitcon.[Codice]) AS Analitico, " _
     & "(SELECT Pdcs.[TipoConto] FROM Pdcs WHERE Pdcs.[conto] = Sitcon.[codice]) AS TipoConto, Sitcon.[Saldo] " _
     & "FROM Sitcon LEFT JOIN Pdcs ON Sitcon.[Codice] = Pdcs.[Conto] " _
     & "WHERE Sitcon.[Azienda] = " & UserForm1.CodAz.Value & " ;", cn

         
  ' Scrive titoli delle colonne
   For iCols = 0 To rts.Fields.Count - 1
      Worksheets("Sitcon").Cells(1, iCols + 1).Value = rts.Fields(iCols).Name
   Next
    
    'Worksheets("Foglio1").Range(Worksheets("Foglio1").Cells(1, 1), _
    'ws.Cells(1, rts.Fields.Count)).Font.Bold = True
    
   ' Popola il foglio
    Worksheets("Sitcon").Range("A2").CopyFromRecordset rts
 
   ' Chiude connessione al database e form
    cn.Close
    UserForm1.Hide
    
    Columns("A:E").EntireColumn.AutoFit
    
    Columns("F:F").Select
    Selection.NumberFormat = "#,##0.00"
     
    Range("A2").Select
    ActiveCell.SpecialCells(xlLastCell).Select
   ' UR = ActiveCell.Row
    
   ' Formatta il Foglio Sitcon
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$F$" & Mid(Str(UR), 2)), , xlYes).Name = "Tabella6"
    Range("Tabella6[#All]").Select
    ActiveSheet.ListObjects("Tabella6").TableStyle = "TableStyleMedium2"
        
   ' Crea il foglio Pivot - se esistente lo pulisce
    Foglio = 2
    On Error GoTo Errore_Foglio
    Sheets.Add.Name = "Pivot"
    Sheets("Pivot").Select
    ActiveWindow.SelectedSheets.Delete
        
        
    ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:="Sheet1!R1C1:R" & UR & "C6", _
    Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Pivot!R3C1", _
        TableName:="Tabella_pivot1", _
        DefaultVersion:=xlPivotTableVersion14
        
        
        
        
        
        
   ' Dispone elementi Pivot
    Sheets("Pivot").Select
    Cells(3, 1).Select
    ActiveSheet.PivotTables("Tabella_pivot1").AddDataField ActiveSheet.PivotTables( _
        "Tabella_pivot1").PivotFields("Saldo"), "Somma di Saldo", xlSum
    With ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("TipoConto")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("Periodo")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("Mastro")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("Intermedio")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("Analitico")
        .Orientation = xlPageField
        .Position = 1
    End With
    
         
   ' Ripristina la visualizzazione normale del video e ripristina l'aspetto del cursore
     Application.ScreenUpdating = True
     Application.Cursor = xlDefault

Exit Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    
''' Gestione errori in fase di creazione del foglio di lavoro "Temporaneo"
Errore_Foglio:

 Debug.Print Err.Number
 
 Select Case Err.Number
        Case 9
             Select Case Foglio
                    Case 1
                         Sheets.Add.Name = "Sitcon"
                    Case 2
                         
              End Select

'       Case Else
              
  End Select
    
' Riprende l'esecuzione dalla riga successiva a quella che ha generato l'errore
  Resume Next

     
End Sub

Comments are in Italian, my language

Can you believe it? I have been working for hours and I didn't see...

However, now I get an 1004 error! I have to investigate.