title: VBA Macro For Naming Ranges
body: <p>I am trying to use the below to run through values in Column A on a Sheet Named "Report" and Create these ranges in a Sheet called "Holidays_Requested" but everytime I it pops up with </p>
  
  <blockquote>
    <p>Object Required Runtime error 424. </p>
  </blockquote>
  
  <p>Can anyone help or know of an alternative way of creating named ranges using VBA. </p>
  
  <pre><code>Sub TransposeRange_new_code()
      Dim OutRange As Range
      Dim x As Long, y As Long
      Dim sKey As String
      Dim maxCount As Long
      Dim data, dic, keys, items, dataout()
  
  
      Application.ScreenUpdating = False
      data = Sheets("Report").Range("A2:E" &amp; Report.Cells(Report.Rows.Count, "A").End(xlUp).Row).Value2
  
  
      Set dic = CreateObject("scripting.dictionary")
      Set OutRange = Sheets("Holidays_Requested").Range("B2")
  
  
      For x = 1 To UBound(data, 1)
          If Trim$(data(x, 1)) &lt;&gt; "_" Then
          sKey = Trim$(data(x, 1)) &amp; Chr(0) &amp; Trim$(data(x, 2))
          If Not dic.exists(sKey) Then dic.Add sKey, CreateObject("Scripting.Dictionary")
          dic(sKey).Add x, Array(data(x, 4), data(x, 5))
          If dic(sKey).Count &gt; maxCount Then maxCount = dic(sKey).Count
          End If
      Next
  
  
      ReDim dataout(1 To maxCount + 1, 1 To dic.Count * 3)
      keys = dic.keys
      items = dic.items
      For x = LBound(keys) To UBound(keys)
          dataout(1, x * 3 + 1) = Split(keys(x), Chr(0))(0)
          dataout(1, x * 3 + 2) = Split(keys(x), Chr(0))(1)
          For y = 1 To items(x).Count
              dataout(1 + y, x * 3 + 1) = items(x).items()(y - 1)(0)
              dataout(1 + y, x * 3 + 2) = items(x).items()(y - 1)(1)
          Next y
      Next
  
  
  
  
      OutRange.Resize(UBound(dataout, 1), UBound(dataout, 2)).Value2 = dataout
  
  
      For x = 1 To UBound(keys)
          OutRange.Offset(0, (x - 1) * 3).Resize(maxCount, 2).Name = "" &amp; validName(Split(keys(x - 1), Chr(0))(0))
          With OutRange.Offset(0, (x - 1) * 3 + 1)
              .Hyperlinks.Add anchor:=.Cells(1), Address:="mailto://" &amp; .Value2, TextToDisplay:=.Value2
          End With
      Next
  
  
  End Sub
  </code></pre>