working with multiple ranges
 

right now, i have something like this:


Code:
--------------------
shDER.Range("C7:C368").Value = Range("LIST").Value
shHum.Range("C7:C368").Value = Range("LIST").Value
shTRI.Range("C7:C368").Value = Range("LIST").Value
shOOS.Range("C7:C368").Value = Range("LIST").Value
--------------------


is there a more elegant way of doing this, perhaps all in one line? all
i'm doing is changing the sheet.

thanks.


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=532989

working with multiple ranges
 

Because the four ranges seems to be in four differnt worksheets and there are
only 4 sheets, I think your current code is already reasonably clean enough.

You may consider putting the sheet names into an array, and then use a for
next loop to assign values to those four sheets. However, it will properly
require more than 4 lines. But that will be a better approach if you got
really a lot of sheets to manipulate.

Regards,
Edwin Tam

http://www.vonixx.com



"dreamz" wrote:


right now, i have something like this:


Code:
--------------------
shDER.Range("C7:C368").Value = Range("LIST").Value
shHum.Range("C7:C368").Value = Range("LIST").Value
shTRI.Range("C7:C368").Value = Range("LIST").Value
shOOS.Range("C7:C368").Value = Range("LIST").Value
--------------------


is there a more elegant way of doing this, perhaps all in one line? all
i'm doing is changing the sheet.

thanks.


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=532989

working with multiple ranges
 

dreamz,

Sub Test()
Dim ws As Worksheet

For Each ws In Worksheets(Array("shDER", "shHum", "shTRI", "shOOS"))
ws.Range("C7:C368").Value =Range("LIST").Value
Next ws
End Sub

You should also qualify your from Range so that it looks somehting like:
Worksheet("Foo").Range("LIST").Value

hth,

Doug

"dreamz" wrote in
message ...

right now, i have something like this:


Code:
--------------------
shDER.Range("C7:C368").Value = Range("LIST").Value
shHum.Range("C7:C368").Value = Range("LIST").Value
shTRI.Range("C7:C368").Value = Range("LIST").Value
shOOS.Range("C7:C368").Value = Range("LIST").Value
--------------------


is there a more elegant way of doing this, perhaps all in one line? all
i'm doing is changing the sheet.

thanks.


--
dreamz
------------------------------------------------------------------------
dreamz's Profile:
http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=532989

working with multiple ranges
 

I would argue that for four sheets, an array is an unnecessary overhead that
makes the code more difficult to understand.

Leave it as it is.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Doug Glancy" wrote in message
...
dreamz,

Sub Test()
Dim ws As Worksheet

For Each ws In Worksheets(Array("shDER", "shHum", "shTRI", "shOOS"))
ws.Range("C7:C368").Value =Range("LIST").Value
Next ws
End Sub

You should also qualify your from Range so that it looks somehting like:
Worksheet("Foo").Range("LIST").Value

hth,

Doug

"dreamz" wrote in
message ...

right now, i have something like this:


Code:
--------------------
shDER.Range("C7:C368").Value = Range("LIST").Value
shHum.Range("C7:C368").Value = Range("LIST").Value
shTRI.Range("C7:C368").Value = Range("LIST").Value
shOOS.Range("C7:C368").Value = Range("LIST").Value
--------------------


is there a more elegant way of doing this, perhaps all in one line? all
i'm doing is changing the sheet.

thanks.


--
dreamz
------------------------------------------------------------------------
dreamz's Profile:
http://www.excelforum.com/member.php...o&userid=26462
View this thread:
http://www.excelforum.com/showthread...hreadid=532989

working with multiple ranges
 

Thanks Bob.

Doug

"Bob Phillips" wrote in message
...
I would argue that for four sheets, an array is an unnecessary overhead
that
makes the code more difficult to understand.

Leave it as it is.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Doug Glancy" wrote in message
...
dreamz,

Sub Test()
Dim ws As Worksheet

For Each ws In Worksheets(Array("shDER", "shHum", "shTRI", "shOOS"))
ws.Range("C7:C368").Value =Range("LIST").Value
Next ws
End Sub

You should also qualify your from Range so that it looks somehting like:
Worksheet("Foo").Range("LIST").Value

hth,

Doug

"dreamz" wrote in
message ...

right now, i have something like this:


Code:
--------------------
shDER.Range("C7:C368").Value = Range("LIST").Value
shHum.Range("C7:C368").Value = Range("LIST").Value
shTRI.Range("C7:C368").Value = Range("LIST").Value
shOOS.Range("C7:C368").Value = Range("LIST").Value
--------------------


is there a more elegant way of doing this, perhaps all in one line? all
i'm doing is changing the sheet.

thanks.


--
dreamz
------------------------------------------------------------------------
dreamz's Profile:
http://www.excelforum.com/member.php...o&userid=26462
View this thread:
http://www.excelforum.com/showthread...hreadid=532989

working with multiple ranges
 

thanks. at the moment, there are only four sheets, but it's a good idea
that i'll have to use later.


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=532989