Solved

Trying to name a range of cells as "database" in a macro

Posted on 2001-07-26
3
151 Views
Last Modified: 2010-05-02
When I try to name a range of cells as "database" in a marco sequence, Visual Basic gives me the following result:    

    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveWorkbook.Names.Add Name:="database", RefersToR1C1:= _
        "='2521'!R31C3:R6651C6"

My problem is I want to use the sequence with various different workbooks. This macro was created with an imported document named '2521' It seems if I want to use the macro with another document, Visual Basic still returns the information based on the earlier document namd.

I did select "relative reference" when I used the macro but that doesn't seem to solve the problem.

I would very much appreciate any help you could provide.

Thank you

Barry
0
Comment
Question by:BarryOTO
  • 2
3 Comments
 
LVL 8

Accepted Solution

by:
DennisBorg earned 50 total points
ID: 6324038
We can use the name of the active worksheet instead of that specific worksheet. Modify the last line as follows:

   ActiveWorkbook.Names.Add Name:="database", RefersToR1C1:= _
       "=" & ActiveWorksheet.Name & "!R31C3:R6651C6"


That should do the trick for you.


-Dennis Borg
0
 

Author Comment

by:BarryOTO
ID: 6335579
The answer gave me all the information I needed to solve my problem.
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6378235
Thank you, Barry. I'm glad I could help.


-Dennis Borg
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
message box in access 4 51
vb6 - Transfer from MSHFlexgrid1 to xls issue 8 56
Child Form in front 4 49
VBA: loop recent folder and copy txt file. 8 36
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

821 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