Setting Up Named Ranges Using VBA

[bloodmilksky]

Hi Guys,

I was just wondering if you could help me.

I was wondering if it is possible to have VBA automatically(using a button) run through Column A setting up a named range for each individual value in column A on sheet 2 to a predetermined size (3x10) going along horizontally.

So Sheet 2 would have named ranges A1:C10, D1:F10, G1:I10 and so on......




Any help would be greatly appreciated.

many thanks

Jamie

 

[wesborland1234]

Yep,

Try this:

    Dim currRange As Range
    Dim currCol As Long
    Dim rangeName As String
    
    For currCol = 1 To 20 Step 3
        Set currRange = Range(Cells(1, currCol), Cells(10, currCol + 2))
        rangeName = "range" & currCol
        ActiveWorkbook.Names.Add Name:=rangeName, RefersTo:=currRange
    Next

Just change the loop from 1 to 20 to however many columns you need this to go for.

 

[Jeffrey Mahoney]

I'm really trying to understand the need for that many named ranges. It seems to me that if these are known areas that you will reference using VBA, it would be easy to create routines.

The ranges you specified are not all in column A (A1:C10, D1:F10, G1:I10 and so on......). Are you wanting to go out to column XFD; which is column 16384; at that rate you would have 5,461 named ranges? What would you name them?

Jeff

 

[bloodmilksky]

Thank you ^_^

It comes up with an error when I try and run it saying "invalid procedure"

 

[bloodmilksky]

Hi Jeff

so the ranges would be named based on the value in column A Sheet 1 and then would be copied to sheet 2 A1:C10, D1:F10, G1:I10 and so on......

 

[Jeffrey Mahoney]

Please give this a try:

Sub MakeNamedRanges()
  Dim Cel As Range
  Dim NRRng As Range
  Dim Sht1 As Worksheet
  Dim Sht2 As Worksheet
  Dim X As Long
  Dim NR As String
  Dim A As String
  Dim Addr As String
  
  Set Sht1 = Sheets("Sheet1")
  Set Sht2 = Sheets("Sheet2")
  
  Set NRRng = Sht1.Range(Sht1.Range("A1"), Sht1.Range("A1000000").End(xlUp))
  X = -2
  For Each Cel In NRRng
    NR = Cel.Value
    X = X + 3
    Addr = Sht2.Range(Sht2.Cells(1, X), Sht2.Cells(10, X + 2)).Address(1, 1)
    A = "='" & Sht2.Name & "'!" & Addr
    Sht2.Names.Add Name:=NR, RefersTo:=A
  Next Cel
  
End Sub

 

[bloodmilksky]

Thank you Jeff Really Appreciate your Help.

Unfortuantly its coming up with a Runtime Error 1004 and doesnt transpose any of the values from sheet 1.