Create table from data on a worksheet with specific name and add column

Question

I have a number of imported worksheets from CSV. The data includes timestamps based on UNIX epoch. Each time I import a new sheet I need to add a column to convert the timestamp to a human readable time.

I used record a macro to get started and got this result:

Sub addnamedtable()
'
' addnamedtable Macro
' Takes the imported data, converts it to a table and then adds a column which uses a formula to read the epoch based date stamp
'
    Range("A1:N5614").Select
    ActiveSheet.QueryTables("Temp6").Delete
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$N$5614"), , xlYes).Name = _
    "Table2"
    Range("Table2[#All]").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    Range("O2").Select
    ActiveCell.FormulaR1C1 = "=[@[s:timestamp]]/(60*60*24*1000)+""1/1/1970"""
    Range("Table2[[#Headers],[Column1]]").Select
    ActiveCell.FormulaR1C1 = "Real_Date"
    Range("O2").Select
End Sub

I then started taking specifics out and replacing them so that it would work with any worksheet and ranges, not just the specific one I had recorded from. This is what I have now.

Sub addnamedtable()
' 
' Takes the imported data, converts it to a table and then adds a column which uses a formula to read the epoch based date stamp
' 

Dim tempname As String
Set tempname = ActiveSheet.Name
    Cells.Select
    ActiveSheet.QueryTables(ActiveSheet.Name).Delete
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$N$5614"), , xlYes).Name = _tempname
    Range(tempname & "[#All]").Select
    Range("O2").Select
    ActiveCell.FormulaR1C1 = "=[@[s:timestamp]]/(60*60*24*1000)+""1/1/1970"""
    Columns("O:O").Select
    Cells.SpecialCells(xlLastCell).Select
    Columns(ActiveCell.Column).Select
    Selection.NumberFormat = "m/d/yyyy h:mm"
    Range(tempname & "[[#Headers],[Column1]]").Select
    ActiveCell.FormulaR1C1 = "Date"
End Sub

I can't get past assigning the sheet name as value to the tempname string. After that I need to name the table based on the sheet to avoid future conflicts in the workbook.

The stuff at the end is probaly nonsense as well but the macro hasn't run through that far to test it.

EDIT Following on from the answer by @Head of Catering

Sub addnamedtable()
'
' addnamedtable Macro
'

    Dim tempname As String
    Dim temprange As Range
    tempname = ActiveSheet.Name
    Cells.Select
    Set temprange = Selection
    ActiveSheet.QueryTables(ActiveSheet.Name).Delete
    ActiveSheet.ListObjects.Add(xlSrcRange, Range(temprange), , xlYes).Name = _
    tempname
    Range(tempname & "[#All]").Select
    Range("O2").Select
    ActiveCell.FormulaR1C1 = "=[@[s:timestamp]]/(60*60*24*1000)+""1/1/1970"""
    Columns("O:O").Select
    Cells.SpecialCells(xlLastCell).Select
    Columns(ActiveCell.Column).Select
    Selection.NumberFormat = "m/d/yyyy h:mm"
    Range(tempname & "[[#Headers],[Column1]]").Select
    ActiveCell.FormulaR1C1 = "Real_Date"
End Sub

Running this gives me the error

Blockquote Method 'Range' of object '_Global' failed

which I think means it doesn't recognise the value of temprange as a range. I tried temprange.address but that caused excel to lock up with no feedback

Answer 1

You don't need the set keyword unless you are setting an object variable.

Change

Set tempname = ActiveSheet.Name

to this:

tempname = ActiveSheet.Name

You have also set temprange equal to all cells in the sheet.

Change

Cells.Select
Set temprange = Selection

to this, just to get past that error:

' Cells.Select -- comment this out, you don't need it
Set temprange = range("A1:J10")

Edit the range to be the one you actually want.

To see what Cells.Select is doing, run this sub and then review the address of the selection in the immediate window.

Sub CellsSelect()
    Cells.Select
    Debug.Print Selection.Address
End Sub