Named Range in VBA

[CEG]

I have the following code:

With Worksheets("filtered")
                .Range("I12") = Worksheets("part number").Range("GN22")

The problem is that row is always changing, so I made a named range called "Subtotals" in the worksheet for that row so it is dynamic, but I don't know how to write the code so it will work. Here is what I have that doesn't work. I always need the column "GN" but the row will go up or down.

With Worksheets("filtered")
.Range("I5") = Worksheets("part number").Cells("Subtotals", "GN")

Thanks
CEG

 

[jeffreybrown]

Try...

Sub test()
Dim Subtotals As Range
Sheets("filtered").Range("I5") = Sheets("part number").Range("Subtotals")
End Sub

 

[CEG]

I guess to rephrase this, I made a row a named range, lets say row 23. Now, it will not always be row 23. So, right now the range is GN23, but tomorrow it could be GN34. Since I have about 20 of these I need to keep track of, I thought the best thing to do was make one row a named range, instead of making about 20 single cells named ranges.

 

[Marcelo Branco]

CEG,

Your named range "Subtotals" uses which columns?

Say it is at columns GL:GN

If you need always refer to column GN (the third column in the named-range in my example) maybe something like

Range("Subtotals").Cells(1,3)

HTH

M.