The range b can only exist on one sheet at a time. It can not span multiple
sheets in the same way that one sheet can not span multiple workbooks. What
you need to do is to copy your range of cells and then paste those to each
target sheet one at a time... Are you only looking to copy the values??? If
so then the code is really very easy as you can just set the values of the
copied range to the destination range something like this...

with sheets("sheet1").range("A1:B1") 'Range to copy
sheets("sheet2").range("A1:B1").Value = .value
sheets("sheet3").range("A1:B1").Value = .value
sheets("sheet3").range("A2:B2").Value = .value
end with
--
HTH...

Jim Thomlinson


"ranswert" wrote:

What I am trying to do is copy a range of values in cells to a range in
different worksheet. The name of the worksheet is in one of the cells. I
thought i could make an array to collect the values in the cells then copy
them to another sheet. The array I called 'a'. 'a(4)' contains the name of
the sheet that I want to copy the values to. Does this not work:

Set b = Sheets(a(4)).Range(Cells(33, 3), Cells(33, 8))

I am trying to set 'b' as the range to copy to.


"Jim Thomlinson" wrote:

A range object can not span multiple sheets. The object hierarchy is the
application contains workbooks, workbooks contain sheets, and sheets contain
ranges. In the same way that a sheet object can not span multiple workbooks a
range object can not span multiple sheets.
--
HTH...

Jim Thomlinson


"ranswert" wrote:

I am trying to get a name of a sheet from a cell then go to that sheet to put
data into a range of cells. I get an error on the following code:

Set b = Sheets(a(4)).Range(Cells(33, 3), Cells(33, 8))

'b' is a range and 'a' is an array(1 to 8) as string

What am I doing wrong?

Thanks