Referencing ranges in different sheets

J

joe_idzhar

Hi all,

I'm quite new to VBA, so I apologies if my knowledge of code is a bi
flaky. What I'm trying to do is a 'For-Each' loop through a larg
range. The code should check the corresponding range in a another shee
and if the value of that range is not empty, copy that range into th
corresponding range in the current sheet. It should do this for eac
cell address (I think).

With this fragment of code:

Dim Bcell As Range

For Each Bcell In Range("A1:D500")
If (Not (IsEmpty(Worksheets("Sheet2").Bcell.Value))) The
Worksheets("Sheet2").Bcell.Value = Worksheets("Sheet2").Bcell.Value
Next Bcell

I keep on getting a run-time error 438, saying that it doesnt suppor
this property or method.

Can anyone help?

Thanks
 
B

Bob Phillips

Dim Bcell As Range

For Each Bcell In Worksheets("Sheet2").Range("A1:D500")
If Not IsEmpty(Bcell.Value) Then
Worksheets("Sheet1").Range(Bcell.Address).Value = Bcell.Value
End If
Next Bcell


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Top