title: VBA: How to programmaticaly create named range pointing to programmatically created sheet?
body: <p><strong>It seems this question also contains proper answer, Excel for some unfathomable reason won't execute it without error. So the question has changed a bit:</strong></p>
  
  <p><strong>Why 1004?</strong></p>
  
  <p>Basically I want to use something like (This give me 1004):</p>
  
  <pre><code>Dim rngTmp As Range
  For Each offer In SanitizedConstInfo("offers").keys()
      Set rngTmp = Sheets(offer).Range(Cells(1, 1), Cells(2, 2))
      ActiveWorkbook.Names.add name:=offer, RefersToR1C1:=rngTmp
      ActiveWorkbook.Names(offer).RefersToRange.Cells(1, 1) = offer
  Next offer
  </code></pre>
  
  <p>offer is string containing some name (Yeah, I want to have both sheet and named range with same name - for now at least).
  Will have unknown number of those, so I just loop <code>for each</code>.</p>
  
  <p>Q: How to add sheet information to <code>RefersToR1C1</code>, so that named range refers to certain sheet? (I know about 'Sheetname'!A1:A10 Syntax but want to do that with Sheet/Range/Cell objects if possible)</p>