How do I assign a variable to the curent work sheet?

I'm writing a small macro that massages data between two sheets in one
work book. What I'd like to do is something like this:

Dim a, b as Worksheet

a = <first worksheet>

b = <second worksheet>

b.cells(1,1) = a.cells(1,1)

etc.


However, the assignments fail.

try something like this

Set A = Worksheets("Sheet1")
Set B = Worksheets("Sheet2")

B.Cells(1, 1).Value = A.Cells(1, 1).Value

--


Gary


"accelerator" <[email protected]> wrote in message
news:[email protected]...
> I'm writing a small macro that massages data between two sheets in one
> work book. What I'd like to do is something like this:
>
> Dim a, b as Worksheet
>
> a = <first worksheet>
>
> b = <second worksheet>
>
> b.cells(1,1) = a.cells(1,1)
>
> etc.
>
>
> However, the assignments fail.
>

try something like this:

Dim A As Worksheet, B As Worksheet

Sub copy_value()

Set A = Worksheets("sheet1")
Set B = Worksheets("Sheet2")

B.Cells(1, 1).Value = A.Cells(1, 1).Value
End Sub

--


Gary


"accelerator" <[email protected]> wrote in message
news:[email protected]...
> I'm writing a small macro that massages data between two sheets in one
> work book. What I'd like to do is something like this:
>
> Dim a, b as Worksheet
>
> a = <first worksheet>
>
> b = <second worksheet>
>
> b.cells(1,1) = a.cells(1,1)
>
> etc.
>
>
> However, the assignments fail.
>

Hi Accelerator,

You need to use Set with object variables.

The following worked for me:

Sub Test()
Dim a As Worksheet, b As Worksheet

Set a = Sheets(1)

Set b = Sheets(2)

b.Cells(1, 1) = a.Cells(1, 1)

End Sub


BTW,

> Dim a, b as Worksheet

is equivalent to:


Dim a as Variant , b as Worksheet


---
Regards,
Norman



"accelerator" <[email protected]> wrote in message
news:[email protected]...
> I'm writing a small macro that massages data between two sheets in one
> work book. What I'd like to do is something like this:
>
> Dim a, b as Worksheet
>
> a = <first worksheet>
>
> b = <second worksheet>
>
> b.cells(1,1) = a.cells(1,1)
>
> etc.
>
>
> However, the assignments fail.
>

When I try this:

Dim a as Worksheet

a = Sheet(1)

I get the error message:

Run-time error 438:

Object doesn't support this property or method

Oh I see! I have to use the "Set" statement.