View Full Version : Solved: Using Named Ranges

08-28-2004, 07:39 AM
I work with several sheets in my workbook that use identical ranges so rather than go through the process of defining them each time I use them I figured it would be easier to just name them and use them from a public module.

What I need to do is to copy the data from the selected sheet (activesheet) to the "Input Sheet". The target cells on the "Input Sheet" are different however. I have a module to handle global code so in the declarations I set:

Public "Range1" as Range

In the module sub I have tried:
sub Copy()

Range("B4:B23").Name = "Range1"

'Lines of code to copy go here

end sub

The problem is that nothing I try seems to be able to copy the values of Range1 to the target cells on the "Input Sheet" without generating a syntax error.

Hopefully this is somewhat clearly stated. How do I work with named ranges?:dunno


Zack Barresse
08-28-2004, 07:49 AM
Hi Ken,

Just to clarify, you're wanting to copy to a different workbook with the same named range, not a different worksheet, right?

08-28-2004, 07:53 AM
Hi Zack,

No it's the same workbook ... different sheet. I found some stuff in the archives about different books but my case is simply sheet to sheet. I'm playing with this code now which does 1 cell but nothing more ... still no error so I'm on the right path.

With Range("Range1")

Sheets("Input Sheet").Range("B5") = Range("Range1").Value

End With


Zack Barresse
08-28-2004, 07:55 AM
Well, named ranges will go cross-sheets. So, imho, it's probably easier to define a range by it's native way isntead of using named ranges 'interchangeably' (so-to-speak) through a workbook. Unless you delete the named range, then re-define. Just seems more troublesome that way.

Can you do it without named ranges? What are the exact requirements? You can always go from one named range and copy to another defined (non-named) range.

08-28-2004, 08:31 AM
I got to thinking that you're probably right. Since I need to always copy the same range of cells from one sheet to a different range on one particular sheet it's easier to use a module sub like this:

sub CopyData()

Sheets("Input Sheet").Range("B5:B24").Value = ActiveSheet.Range("B4:B23").Value

end sub

Since it's in a public module it will work every time with the single (traditional) range method.


Zack Barresse
08-28-2004, 08:50 AM
Sure Ken. If there's anything else you need help with don't hesitate to post back. I'm sure you'd get a few excellent methods to accomplish whatever you're trying to do. That's one benefit of having a collection of the world's finest coders (imho) here at VBAX! :)

Take care!