Creating a Table with Changing Ranges

[Reebs]

Hi, first time poster here!

I have a data set that gets updated weekly and need the table created in my macro to account for this. Heres the code I've got:

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$O$104017"), , xlNo).Name _
= "ShotData"

Thanks in advance!
-Reebs

 

[JEC]

Did you try power query to import the data?

 

[Reebs]

Did you try power query to import the data?

No, I've been using multiple macros like this to import my data sets which works pretty well.

Sub Get_Shot_Data()
    
    Application.ScreenUpdating = False
    
    Dim target_workbook As Workbook
    Dim data_sheet As Worksheet
    Dim folder_path As String, my_file As String
    Dim LastRow As Long
    
    Set data_sheet = ThisWorkbook.Worksheets("Shot Data")
    
    folder_path = "J:\Public\TI OPS TOOLS\X-Ray Tracker\Newest Exports\"
    
    my_file = Dir(folder_path & "*.csv")
    
    '// Step 1: Clear worksheet
    If my_file = vbNullString Then
        MsgBox "CSV files not found.", vbInformation
    Else:
        data_sheet.Cells.ClearContents
    End If
    
    '// Step 2: Iterate CSV Files
    Do While my_file <> vbNullString
        Set target_workbook = Workbooks.Open(folder_path & my_file)
            
        LastRow = data_sheet.Cells(Rows.Count, "A").End(xlUp).Row
        
        target_workbook.Worksheets(1).Range("A1").CurrentRegion.Copy data_sheet.Cells(LastRow + 1, "A")
        target_workbook.Close False
        
        Set target_workbook = Nothing
        
        my_file = Dir()
    Loop

    '// Step 3: Clean up
    data_sheet.Rows(1).Delete
    data_sheet.Range("A1").CurrentRegion.RemoveDuplicates 1, xlNo
    
    Set data_sheet = Nothing

Application.ScreenUpdating = True

End Sub

This takes three .csv files and combines them very nicely for me but I still have the issue with creating a table from the changing ranges.