VBA: Reference a named range by a string with the name of the range in it

[sickvisionz]

I have a spreadsheet with about 30 named ranges in it. I need to cycle through 10 of them and do with style process to each. I thought it would be easiest to make another named range called "tableList" that is a one column table with the names of the tables I need and access it something like this

The basic structure of the code would be

dim cell, temprange as Range

For Each cell in [tableList]
    Set temprange = [cell] 'this is supposed to represent the string value in cell being used as a named range
    With temprange
[I]        with stuff you can do to a range[/I]
        end With
Next cell

If the first name in tableList is "Jimmy" and the second is "Bob", the goal is that the code copies the first column in the named range called "Jimmy" onto the MASTERLIST. It goes to the next cell and copies the first column in the named "Bob" to the master list, etc.

I can't get this to work for the life of me. I can get something like it functional using for each [name variable] in Activeworkbook.Names but I don't need every named ranged to be processed like this, just specific ones.

 

[Scott Huish]

Re: VBA: Reference a name range by a string with the name of the range in it

Set temprange = Range(cell)

 

[sickvisionz]

Re: VBA: Reference a name range by a string with the name of the range in it

Set temprange = Range(cell)

When I try that code, the line generates a "Run-time error '1004': Application-defined or object-defined error"

This is the full code that I have if it helps any

    Dim tblCell As Range
    Dim tempRange As Range
    Dim mLStart As Integer
    
    With MASTERLIST 'clear MASTERLIST and make headers
        .Range("A:Z").ClearContents
        .Range("A1:E1").Font.Bold = True
        .Range("A1").Value = "Ship #"
        .Range("B1").Value = "Ship Type"
        .Range("C1").Value = "Dom/Int"
        .Range("D1").Value = "Status"
        .Range("E1").Value = "Last Service"
    End With
   
    For Each tblCell In [nametbl]
         mLStart = WorksheetFunction.CountA(MASTERLIST.Range("a:a")) + 1
        Set tempRange = Range(tblCell)
         tempRange.Columns(1).Copy
        MASTERLIST.Cells(mLStart, 1).PasteSpecial xlPasteValues
        MASTERLIST.Cells(mLStart, 1).EntireRow.Delete
    Next tblCell

I've copied and pasted directly from the name manager to nametbl so the strings it's feeding the For statement should be accurate.

 

[Sektor]

Re: VBA: Reference a name range by a string with the name of the range in it

Check your "nametbl" named range. Is it OK?

 

[sickvisionz]

Re: VBA: Reference a name range by a string with the name of the range in it

Check your "nametbl" named range. Is it OK?

Yeah. If insert something like "msgbox tblCell" prior to the Set, it spits out a string with the first cell in nametbl. I think that part is ok.

 

[Sektor]

Re: VBA: Reference a name range by a string with the name of the range in it

There's possibility that your named ranges are worksheet level - not workbook. Check this out in Name Manager.

 

[sickvisionz]

Re: VBA: Reference a name range by a string with the name of the range in it

Thanks for the help. I should have mentioned that it was a named table and not just a named range. I ended up finding a work around by making a named range inside of the table. This range was the list of named tables in the workbook. With that, I could get everything working normally.

I feel like a better understanding of list objects and arrays may have helped me... or perhaps just Access VBA. Anyhoo, thanks.