title: How to rename range using VBA in excel 2007
body: <p>I've 8+ yes professional programming experience in PHP and Java but none in VBA. Learning it right now.</p>
  
  <p>I'm trying to make a Home budget sheet (Just for VBA learning purpose). For that, I'd done following</p>
  
  <pre><code>* In new excel (2007) file, rename sheet 1 as 'Forms' And Sheet2 as 'CatAcc'
  * In sheet 'CatAcc', I'm using (planning) column A for categories and B for Account
  * Row 1 is heading (A1 = "Categories", B1="Account"
  * Using forms sheet (cell C2) &amp; VBA button, I want to add a new category,
    sort it alphabetically and then rename range to add newly added row.
  </code></pre>
  
  <p>I wrote following code for that (Recorded testmacro to check how to name a range)</p>
  
  <pre><code>Sub AddCat_Click()
      'Copy data as last row
      Worksheets("CatAcc").Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = Worksheets("Forms").Cells(2, "C").Value
  
      'Find total Rows in categories
      Dim totalRows
      totalRows = Worksheets("CatAcc").Range("A2").End(xlDown).Row
  
      'Define Range
      Dim rng
      rng = "A2:A" &amp; totalRows
      'MsgBox rng
  
      'Select the range - Getting error in following line.
      Worksheets("CatAcc").Range(rng).Select
  
      'Name the range
      ActiveWorkbook.Names.Add Name:="categories", RefersToR1C1:="=CatAcc!R2C1:R3C1"
  
      'Sort range alphabetically
  
      'Apply range as drop-down options
  End Sub
  Sub testmacro()
      Range("A2:A3").Select
      ActiveWorkbook.Names.Add Name:="categories", RefersToR1C1:= _
          "=CatAcc!R2C1:R3C1"
  End Sub
  </code></pre>
  
  <p>While selecting the range, I got following error</p>
  
  <pre><code>Run-time error '1004'
  Select method of Range class failed
  </code></pre>
  
  <p>I'm unable to understand what that error mean and why I'm getting that by just adding worksheet name.</p>
  
  <p>Again, What does following line mean? What is happening there? I could not understand meaning of R2C1:R3C2. This values came from recorded macro when I named A2:A3 cells as 'categories'</p>
  
  <pre><code>ActiveWorkbook.Names.Add Name:="categories", RefersToR1C1:="=CatAcc!R2C1:R3C1"
  </code></pre>