title: Is there a way to make an Excel table dynamic using VBA?
body: <p>I'm importing raw data from an external source and pasting it over the existing raw data. I have a table named "dataTbl' that needs to updated when new data is pasted over existing data. Can this be done through VBA?</p>
  
  <p>I've recorded the below code using Excel macro recorder to alter the existing "dataTbl" with the new data. When converting to VBA however, it appears that the data is not dynamic and cannot handle a variable. Perhaps there is another way to do this.</p>
  
  <pre><code>Option Explicit
  Dim lastR As Long
  Dim lastC As Long
  Dim startCell As Range
  Dim tblSource As Range
  Dim wsSource As Worksheet
  Dim wb As Workbook
  
  Sub refresh()
  '
  'Refresh Excel table with newest Oracle data
  '
  
  Set wb = Workbooks("OTR_Dashboard")
  Set wsSource = wb.Sheets("RawData")
  
  With wsSource
      Set startCell = Range("A1")
      lastR = wsSource.Cells(Rows.Count, 1).End(xlUp).Row
      lastC = wsSource.Cells(3, Columns.Count).End(xlToLeft).Column
  End With
  
  Set tblSource = wsSource.Range(wsSource.Cells(1, 1), wsSource.Cells(lastR, lastC))
  
  Sheets("RawData").Range("A1").Select
      Application.CutCopyMode = False
      'ActiveWorkbook.Name("dataTbl").RefersToR1C1 = tblSource
      ActiveWorkbook.Names("dataTbl").RefersToR1C1 = "=RawData!R1C1:R87671C28"
      Application.CutCopyMode = False
  
  Sheets("DASHBOARD").Select
      ActiveSheet.ChartObjects("Chart 1").Activate
      ActiveWorkbook.RefreshAll
  
  End Sub
  </code></pre>
  
  <p>The commented out line (RefersToR1C1 = tblSource) gives the error message "compile error: wrong number of arguments or invalid property assignment". The line below it, without dynamic data, works without any issues.</p>