CODEWITHSUNDEEP
vbaexcel
Elsybelsy
Elsybelsy

Reputation: 71

Method 'Range' of object '_worksheet' failed when finding a range

I'm attempting to write a macro to copy data from one worksheet to another. However, I'm struggling on writing an if statement - if the active worksheet is x (in my case VFO_W1_W2), then delete all data in the Cons worksheet (within a range).

Sub CopyVFOW1_W2()
Dim Cons As Worksheet
Dim Active As Worksheet
Dim FirstBlank As Range

Set Active = ActiveSheet
Set Cons = Sheets("VFO_CONS")
Set FirstBlank = Cons.Range("B1").End(xlDown).Offset(1, 0)

If Application.ActiveSheet.Name = ("VFO_W1_W2") Then Cons.Range("A4:Z4", Range("A4,Z4").End(xlDown)).Delete Shift:=xlUp

Active.Range("B8:Z8", Range("B8:Z8").End(xlDown)).Copy
Cons.Activate
FirstBlank.PasteSpecial Paste:=xlPasteFormulas, Transpose:=False

End Sub

In particular, this line of code seems to be the issue

Cons.Range("A4:Z4", Range("A4,Z4").End(xlDown)).Delete Shift:=xlUp

The error states that the method 'range' of object '_worksheet' has failed.

Any help would be appreciated.

Upvotes: 0

Views: 412

Answers (1)

SJR
SJR

Reputation: 23081

Add sheet references to both as the error will arise if a different sheet is active when you run the code (the range will straddle two sheets). ALso I wouldn't rely on the active sheet, but define Active in terms of its name.

Cons.Range("A4:Z4", Cons.Range("A4,Z4").End(xlDown)).Delete Shift:=xlUp

Upvotes: 1

Related Questions