• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2187
  • 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
NorieVBA ExpertCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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