Referencing ranges in different sheets

Hi all,

I'm quite new to VBA, so I apologies if my knowledge of code is a bit flaky. What I'm trying to do is a 'For-Each' loop through a large range. The code should check the corresponding range in a another sheet and if the value of that range is not empty, copy that range into the corresponding range in the current sheet. It should do this for each 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))) Then Worksheets("Sheet2").Bcell.Value = Worksheets("Sheet2").Bcell.Value
Next Bcell

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

Can anyone help?

Thanks!

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)

"joe_idzhar" <joe_idzhar.2cvx2e_1156154705.036@excelforum-nospam.com> wrote
in message news:joe_idzhar.2cvx2e_1156154705.036@excelforum-nospam.com...
>
> Hi all,
>
> I'm quite new to VBA, so I apologies if my knowledge of code is a bit
> flaky. What I'm trying to do is a 'For-Each' loop through a large
> range. The code should check the corresponding range in a another sheet
> and if the value of that range is not empty, copy that range into the
> corresponding range in the current sheet. It should do this for each
> 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))) Then
> Worksheets("Sheet2").Bcell.Value = Worksheets("Sheet2").Bcell.Value
> Next Bcell
>
> I keep on getting a run-time error 438, saying that it doesnt support
> this property or method.
>
> Can anyone help?
>
> Thanks!
>
>
> --
> joe_idzhar
> ------------------------------------------------------------------------
> joe_idzhar's Profile:
http://www.excelforum.com/member.php...o&userid=37487
> View this thread: http://www.excelforum.com/showthread...hreadid=573687
>

Savage stuff, thanks Bob!