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 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 - 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
Note rng already contains which Worksheet it belongs to. Sheets(5).rng is incorrect. — John Alexiou

Chrismas007 Chrismas007 · Accepted Answer · 2015-01-22T16:12:41

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)

Using range-variable in multiple worksheets

1 Answers