title: VBA Dynamic Ranges
body: <p>I wonder whether someone may be able to help me please.</p>
  
  <p>I've put together the code below which creates a new sheet in my workbook and applies dynamic named ranges and page formatting.</p>
  
  <pre><code>Sub AllDataNamedRanges()
  
  Dim rLOB As Range
  Dim rStaffName As Range
  Dim rTask As Range
  Dim rProjectName As Range
  Dim rProjectID As Range
  Dim rJobRole As Range
  Dim rMonth As Range
  Dim rActuals As Range
  
  Set rLOB = Range([B4], [B4].End(xlDown))
  Set rStaffName = Range([C4], [C4].End(xlDown))
  Set rTask = Range([D4], [D4].End(xlDown))
  Set rProjectName = Range([E4], [E4].End(xlDown))
  Set rProjectID = Range([F4], [F4].End(xlDown))
  Set rJobRole = Range([G4], [G4].End(xlDown))
  Set rMonth = Range([H4], [H4].End(xlDown))
  Set rActuals = Range([I4], [I4].End(xlDown))
  
  Sheets("AllData").Select
  
      ActiveWorkbook.Names.Add Name:="LOB", RefersToR1C1:="=" &amp; _
      ActiveSheet.Name &amp; "!" &amp; rLOB.Address(ReferenceStyle:=xlR1C1)
  
      ActiveWorkbook.Names.Add Name:="StaffName", RefersToR1C1:="=" &amp; _
      ActiveSheet.Name &amp; "!" &amp; rStaffName.Address(ReferenceStyle:=xlR1C1)
  
      ActiveWorkbook.Names.Add Name:="Task", RefersToR1C1:="=" &amp; _
      ActiveSheet.Name &amp; "!" &amp; rTask.Address(ReferenceStyle:=xlR1C1)
  
      ActiveWorkbook.Names.Add Name:="ProjectName", RefersToR1C1:="=" &amp; _
      ActiveSheet.Name &amp; "!" &amp; rProjectName.Address(ReferenceStyle:=xlR1C1)
  
      ActiveWorkbook.Names.Add Name:="ProjectID", RefersToR1C1:="=" &amp; _
      ActiveSheet.Name &amp; "!" &amp; rProjectID.Address(ReferenceStyle:=xlR1C1)
  
      ActiveWorkbook.Names.Add Name:="JobRole", RefersToR1C1:="=" &amp; _
      ActiveSheet.Name &amp; "!" &amp; rJobRole.Address(ReferenceStyle:=xlR1C1)
  
      ActiveWorkbook.Names.Add Name:="Month", RefersToR1C1:="=" &amp; _
      ActiveSheet.Name &amp; "!" &amp; rMonth.Address(ReferenceStyle:=xlR1C1)
  
      ActiveWorkbook.Names.Add Name:="Actuals", RefersToR1C1:="=" &amp; _
      ActiveSheet.Name &amp; "!" &amp; rActuals.Address(ReferenceStyle:=xlR1C1)
  
  End Sub
  </code></pre>
  
  <p>The code does work but I'm a little concerned that it may be a little clunky and could be written smarter. I'm relatively new to VBA but I'm willing to learn.</p>
  
  <p>I just wondered whether someone, who is perhaps a more seasoned programmer than I, could look at this please and offer some guidance on how I may be able to write this a little better.</p>
  
  <p>Many thanks and kind regards</p>