CODEWITHSUNDEEP
excelvbadynamic
radeg
radeg

Reputation: 61

Is there a way to make an Excel table dynamic using VBA?

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?

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.

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

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.

Upvotes: 0

Views: 786

Answers (1)

radeg
radeg

Reputation: 61

Assining to .RefersTo instead of RefersToR1C1 ended up working:

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 dashRefresh()
'
'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))

With ActiveWorkbook.Names("dataTbl")
    .Name = "dataTbl"
    .RefersTo = tblSource
    .Comment = ""
End With
Application.CutCopyMode = False

Sheets("DASHBOARD").Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveWorkbook.RefreshAll

End Sub

Upvotes: 2

Related Questions