CODEWITHSUNDEEP
excelvba
Philipp Schwind
Philipp Schwind

Reputation: 1

Set Range from another Sheet

When defining Ranges on a specific Sheet Excel throws Error 1004

I got two Sheets:
-Sheet A
-Sheet B

Scope Sheet_A:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng as Range
    Set Rng = Sheets("Sheet_B").Range("A1")
    Set Rng = Range(Rng, Rng.offset(0,1))
End Sub

Maybe I protected my Sheet or something like this, because I get:

1004 "application-defined or object-defined error"

Surprisingly this doesn't throw an Error, but Functions like Rng.Select or others don't work either:

   Set Rng = Range(Rng.Address, Rng.offset(0,1).Address)

By the way:
Doing the same from Sheet_B to Sheet_A works.

Upvotes: 0

Views: 1002

Answers (1)

M.Doerner
M.Doerner

Reputation: 722

As @BigBen already hinted at in his comment, the error is raised because you try to get a range on one sheet between ranges on another sheet. Excel cannot do this.

Since the code is in the code behind of Sheet A, the following code is equivalent to your code.

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Rng as Range
   Set Rng = ActiveWorkbook.Sheets("Sheet_B").Range("A1")
   Set Rng = Me.Range(Rng, Rng.offset(0,1))
End Sub

Here Me refers to Sheet A.

To get the probably desired result, you can either explicitly qualify the call to Range with something referring to Sheet B, e.g. ActiveWorkbook.Sheets("Sheet_B"), Rng.Parent or some reference you have saved before, or you can resize your range in a different way. One example of the latter was already mentioned in the comments, using Resize.

Upvotes: 1

Related Questions