Relative References in Excel 2011 Mac for Dynamic Report Macros (VBA) -

input:

text file (csv) apple remote desktop on application usage dynamic series of macs.

goal:

to convert csv file apple remote desktop easy-to-read format using series of excel macros (or other options possible). used among numerous teams, amount of data changing in every report.

methodology far:

i'm doing in series of small macros, make easier troubleshoot. primary issue way ard records application usage: every instance of application starting , shutting down , length of time running in separate row. in row there 2 kinds of time: "front most" , "front in seconds."

"front most" recorded in format: "1h, 5m" making impossible use sort of total. "front in seconds" recorded standard number , focus of project.

macros:

here macros i've built without issue:

macro 1: ard_convert1_import - import text file predetermined location new sheet in existing workbook. contains basic conversion csv excel.

macro 2: ard_convert2_columns - makes minor adjustments columns, including adding columns allow calculation of seconds hours, along removing unnecessary columns in report. (note: columns same in every worksheet. it's rows change.)

macro 3a: ard_convert3_tableselect - supposed select entire table dynamically, perfectly.

sub ard_convert3_tableselect()     range("a1:a" & cells(rows.count, "b").end(xlup).row).select     range(selection, selection.end(xltoright)).select end sub 

macro 3b: ard_convert3_wholetable - problem point. need convert selected information in "3a" table. however, "relative references" selected, when record macro, has absolute references built in.

example:

sub ard_convert3_wholetable()     activesheet.listobjects.add(xlsrcrange, range("$a$1:$j$284"), , xlyes).name = _         "table3"     activecell.range("table3[#all]").select     activesheet.listobjects("table3").tablestyle = "tablestylelight1" end sub 

my biggest problem absolute references keep sneaking code , cannot figure out how make them relative references. i've tried create dynamic named ranges use in range("$a$1:$j$284") field. i've tried dozen different ideas , have come totally empty.

this last piece need this.

please help!

-rks

merging 2 macros:

sub ard_convert3_wholetable()     activesheet.listobjects.add(xlsrcrange, _                      activesheet.range("a1").currentregion, , xlyes)          .name = "table3"         .tablestyle = "tablestylelight1"      end end sub