[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1903
  • Last Modified:

Trying to Set a Range referring to an inactive worksheet return Application-defined/Object-defined Error

Please can someone help with this simple error, which I cannot seem to understand...
In the attached workbook there are two worksheets.

I have a macro to set a range on the inactive sheet by clicking a command button on the active sheet.

It appears that to set a range, the sheet has to be active, or returns an  Error 1004 "object-defined or application-defined" error.

Am I missing something, or do I have to first activate the sheet where I want to set the range?

This is the code:
Option Explicit
Sub SetRangesAndNames()

    Dim NewRange As Range
    Dim ListofVendors As String
'Set Range you wish to use (NewRange)
    Set NewRange = _
       Sheets("ThirdNews").Range(Cells(1, 1), Cells(1, 1).End(xlDown))
    MsgBox NewRange.Address
    ActiveWorkbook.Names.Add Name:="ListofVendors", RefersTo:=NewRange
End Sub

Open in new window

Thanks very much
David Phelops
David Phelops
1 Solution
The worksheet doesn't need to be active.

However you need to reference everything correctly, which is where your code goes wrong.

Here Cells don't have worksheet references so they will refer to the active worksheet.
Sheets("ThirdNews").Range(Cells(1, 1), Cells(1, 1).End(xlDown))

Open in new window

It's easy to fix using a With and some well placed dots.
With Sheets("ThirdNews")
    Set NewRange = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
End With

Open in new window

David PhelopsAuthor Commented:
Thank you - worked a treat.
Now all I have to do is understand it...

Thanks very much for the prompt help.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now