title: Relative References in Excel 2011 Mac for Dynamic Report Macros (VBA)
body: <p><strong>Input:</strong></p>
  
  <p>Text File (CSV) from Apple Remote Desktop on Application Usage for a dynamic series of Macs.</p>
  
  <p><strong>Goal:</strong></p>
  
  <p>To convert the CSV File from Apple Remote Desktop into an easy-to-read format using a series of Excel Macros (or other options as possible). This will be used among numerous teams, so the amount of data will always be changing in every report.</p>
  
  <p><strong>Methodology So Far:</strong></p>
  
  <p>I'm doing this in a series of small macros, primarily to make it easier to troubleshoot. The primary issue is the way ARD records Application Usage: every instance of an application starting and shutting down and the length of time it was running is in a separate row. In this row there are two kinds of time: "Front Most" and "Front Most in Seconds."</p>
  
  <p>"Front Most" is recorded in this format: "1h, 5m" making it impossible to use for any sort of total.
  "Front Most in Seconds" is recorded as a standard number and is the focus of this project.</p>
  
  <p><strong>Macros:</strong></p>
  
  <p>Here are the macros I've successfully built without issue:</p>
  
  <p><em>Macro 1</em>: ARD_Convert1_Import - Import text file from predetermined location into a new sheet in an existing workbook. This contains just the basic conversion from CSV to Excel.</p>
  
  <p><em>Macro 2</em>: ARD_Convert2_Columns - Makes some minor adjustments to the columns, including adding columns to allow for the calculation of seconds into hours, along with removing the unnecessary columns in the report. (Note: the columns are always the same in every worksheet. It's the rows that change.)</p>
  
  <p><em>Macro 3a</em>: ARD_Convert3_TableSelect - This is supposed to select the entire table dynamically, which it does perfectly.</p>
  
  <pre><code>Sub ARD_Convert3_TableSelect()
      Range("A1:A" &amp; Cells(Rows.Count, "B").End(xlUp).Row).Select
      Range(Selection, Selection.End(xlToRight)).Select
  End Sub
  </code></pre>
  
  <p><strong><em>Macro 3b</em></strong>: ARD_Convert3_WholeTable - This is my problem point. I need to convert the selected information in "3a" into a table. However, even with "Relative References" selected, when I record the macro, it always has absolute references built in. </p>
  
  <p>Example:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>My biggest problem with this is that absolute references keep sneaking into my code and I cannot figure out how to make them relative references. I've tried to create dynamic named ranges to use in the Range("$A$1:$J$284") field. I've tried a dozen different ideas and have come up totally empty.</p>
  
  <p>This is the LAST piece I need for this.</p>
  
  <p>Please help! </p>
  
  <p>-rks</p>