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.

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

try something like this

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

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

--


Gary


"accelerator" wrote in message
oups.com...
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.

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

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" wrote in message
oups.com...
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.

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

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" wrote in message
oups.com...
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.

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

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.