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

Posted on 2012-09-15
Last Modified: 2012-09-15
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
Question by:David Phelops
    LVL 33

    Accepted 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


    Author Closing Comment

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

    Thanks very much for the prompt help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Suggested Solutions

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now