Application Defined or Object defined error

Posted on 2011-10-06
Last Modified: 2012-05-12
Set BaseWks = ActiveSheet

Sheets("GL Summary").Range("A1:G").CurrentRegion.Name = "Ranges"

Open in new window


I am getting the 'Application Defined or Object defined error' on above line.

Using examples in this forum, I tried this

Set BaseWks = ActiveSheet

basewks.Range("A1:G").CurrentRegion.Name = "Ranges"

Open in new window

Then it gave me, 'method range of object worksheet failed' error :o

Any help is much appreicated!
I am trying to assign the region as an array for my dynamic pivot table!


Question by:Shanan212
    LVL 24

    Expert Comment

    Try thus:
    basewks.Range("A1").CurrentRegion.Name = "Ranges"

    Open in new window

    LVL 13

    Accepted Solution

    I tried that originally but that picked up unnessary data outside of column G. I got it to work now using this!

        Dim tblRng As Range
        Set tblRng = BaseWks.Range("A1:G" & BaseWks.Range("A" & BaseWks.Rows.Count).End(xlUp).Row)
        tblRng.Name = "Ranges"

    Open in new window

    LVL 13

    Author Closing Comment

    this works now
    LVL 24

    Expert Comment

    Perhaps you can get in the habit of testing things before you ask the question.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

    760 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

    9 Experts available now in Live!

    Get 1:1 Help Now