Vb to redefine range names

Posted on 2007-10-16
Last Modified: 2012-08-14
I have a range named 'TEST' in a workbook which comes from another worksheet ('MASTER') in the same workbook.  The file gets replaced on a weekly basis and I would like to automate redefining the range through vb.  If the range for 'TEST' is currently set to ='Master'!$B$2:$F$69.  I need to find the last cell in the new test and extend the range.  I hope this makes sense.
Question by:ilrosebud26
    LVL 33

    Accepted Solution

    You might try a macro like this one:

    Sub ResetDefinedName()
    RowsA = Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Row
    ActiveWorkbook.Names.Add Name:="TEST", RefersToR1C1:="Master!R6C2: R" & RowsA & "C6" & Chr(32)
    End Sub


    Author Comment

        Thanks for the quick response.  I will try that out.

    LVL 33

    Expert Comment

    If you need further assitance let me know.

    Author Comment

    With a slight modification I was able to use is great to get a correct response so quickly!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    733 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

    20 Experts available now in Live!

    Get 1:1 Help Now