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

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

Hi

if = 1 as string It looks like your trying to select "Sheet14. If so try this

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

Mike

"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

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

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

Move the "Sheets(a(4))." part inside in front of the "cells(..." like this,

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

or
With Sheets(a(4))
set b = range(.cells(33,3),.cells(33,8)
End With

then, of course, a(4) in the a array must contain a string corresponding to
an existing Sheet in the workbook.
The problem with your code is that the Cells(33,3) refers to the active
worsheet, even though you stated the sheet in front of your range. In
principle, what you have been writing is
Set b = Sheets(a(4)).Range(ActiveSheet.cells(33, 3), ActiveSheet.Cells(33,
8))

Your code actually works if you set a(4) to the active workbook before
setting b to your range.

regards,
Lazzzx




"Jim Thomlinson" skrev i
meddelelsen ...
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

Thanks for your help I'll give that a try
"Jim Thomlinson" wrote:

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

Take a look at this:
http://www.rondebruin.nl/copy2.htm

I believe you can modify it (slightly) for your purpose.


Regards,
Ryan--


--
RyGuy


"Mike H" wrote:

Hi

if = 1 as string It looks like your trying to select "Sheet14. If so try this

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

Mike

"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