Using range-variable in multiple worksheets

Question

I have an issue while using a variable containing a range.

I've declared the variable "rng" globally in the workbook module:

Public rng As Range

Now in a worksheet module I set the variable after clicking on a checkbox and define a range for it:

Sub CheckBox1_Click()
    Set rng = Range("D8:Q51")

    If Me.OLEObjects("checkbox1").Object.Value Then
        Call clear(rng)
    Else
        Call aus(rng)
    End If
End Sub

I always get an error when calling the sub "aus(rng)" which says:

error 438, object doesn't support this property or method

"aus(rng)" contains the following code:

Worksheets(5).rng.Copy Worksheets("aktuell").rng

Btw: using the range-variable in the same worksheet the module is connected to doesn't throw out an error. So the error somehow has to correlate with "Worksheets(5)".

Define rng in a normal module, not a workbook or worksheet module. — Gareth
– Gareth, Commented Jan 22, 2015 at 16:04
@Gareth When rng is defined, isn't it grabbing the default book and sheet? So basically it is like writing Set rng = ActiveWorkbook.ActiveSheet.Range("D8:Q51")... — Chrismas007
– Chrismas007, Commented Jan 22, 2015 at 16:07
@Chrismas007 - Even if there was no confusion in the wb/ws parent of rng, the Call aus(rng) attempts to redefine it as belonging to Worksheets(5). It seems to be used closer to a rng.Address than a rng. There is also no need to even pass it across as a parameter if it is declared globally. — user4039065
– user4039065, Commented Jan 22, 2015 at 16:11
Note rng already contains which Worksheet it belongs to. Sheets(5).rng is incorrect. — John Alexiou
– John Alexiou, Commented Jan 22, 2015 at 16:42

Chrismas007 · Accepted Answer · 2015-01-22 16:50:36Z

4

When you define a Range with Set and you don't define the parent objects of Workbook or Worksheet, it will default to ActiveWorkBook and ActiveWorkSheet. Therefore it is like writing:

Set rng = ActiveWorkbook.ActiveSheet.Range("D8:Q51")

When you use the rng later in the code, you try and assign it under a Sheet which is basically writing:

Worksheets(5).ActiveWorkbook.ActiveSheet.Range("D8:Q51").Copy

Which we know is incorrect syntax and will error.

As a workaround, you could use the following code:

Worksheets(5).Range(rng.Address).Copy Worksheets("aktuell").Range(rng.Address)

edited Jan 22, 2015 at 16:50

answered Jan 22, 2015 at 16:12

Chrismas007

6,1154 gold badges27 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using range-variable in multiple worksheets

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related