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

i have number of imported worksheets csv. data includes timestamps based on unix epoch. each time import new sheet need add column convert timestamp human readable time.

i used record macro started , got result:

sub addnamedtable() ' ' addnamedtable macro ' takes imported data, converts table , adds column uses formula read 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 started taking specifics out , replacing them work worksheet , ranges, not specific 1 had recorded from. have now.

sub addnamedtable() '  ' takes imported data, converts table , adds column uses formula read epoch based date stamp '   dim tempname 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 past assigning sheet name value tempname string. after need name table based on sheet avoid future conflicts in workbook.

the stuff @ end probaly nonsense macro hasn't run through far test it.

edit following on answer @head of catering

sub addnamedtable() ' ' addnamedtable macro '      dim tempname string     dim temprange 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 gives me error

blockquote method 'range' of object '_global' failed

which think means doesn't recognise value of temprange range. tried temprange.address caused excel lock no feedback

you don't need set keyword unless setting object variable.

change

set tempname = activesheet.name 

to this:

tempname = activesheet.name 

you have set temprange equal cells in sheet.

change

cells.select set temprange = selection 

to this, past error:

' cells.select -- comment out, don't need set temprange = range("a1:j10") 

edit range 1 want.

to see cells.select doing, run sub , review address of selection in immediate window.

sub cellsselect()     cells.select     debug.print selection.address end sub