Including tab name in absolute reference

I have lots of spreadsheets that have the same seven tabs in them.  How do I 
automatically ensure that the correct tab is selected when running a macro 
with absolute references?  Is there a way to have the macro select the tab 
before running the rest of the macro?

Brad

Excel 2002 on XP Pro SP 3
Excel 2007 on Vista 64 


0
Brad
1/14/2010 3:50:39 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
654 Views

Similar Articles

[PageSpeed] 18

The answer is "NOT TO USE the SELECT METHOD".  In your code reference
each sheet by its object or Name.  Don't use recorded macros without
eliminating all the select properties and activate properties (there are
only a few cases where this may not work).


Selection of TABS slows down the macro and can create errors like you
are finding out.  Here are some examples of the correct method of
referencing objects


set bk = workbooks.open(Filename:=Book1.xls)

set sht = bk.sheets("sheet1")

for each sht in bk.sheets


set DataRange = sht.rangge("A1:B100")


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=170049

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

0
joel
1/14/2010 5:04:25 PM
Your post is kinda vague, plus you didn't post any of your current code.  
When you say "spreadsheets" do you mean workbooks?  And when you say "tabs" I 
assume you mean worksheets within the workbook, right?  But I think you want 
to activate a sheet in a particular workbook before a particular macro is 
ran, right?  If so, you can use this code below.  It sounds to me you need a 
loop referencing workbooks and sheets, which would be more efficient.  Hope 
this helps!  If so, let me know, click "YES" below.

Sub YourSub()

   Workbooks("Book1.xls").Sheets("Sheet1").Activate

   ' your code for that worksheet here

   Workbooks("Book1.xls").Sheets("Sheet2").Activate

   ' your code for that worksheet here
   'etc

End Sub
-- 
Cheers,
Ryan


"Brad" wrote:

> I have lots of spreadsheets that have the same seven tabs in them.  How do I 
> automatically ensure that the correct tab is selected when running a macro 
> with absolute references?  Is there a way to have the macro select the tab 
> before running the rest of the macro?
> 
> Brad
> 
> Excel 2002 on XP Pro SP 3
> Excel 2007 on Vista 64 
> 
> 
> .
> 
0
Utf
1/14/2010 5:28:01 PM
Joel,

Thank you for your help.  It gave me what I was looking for.

I understand the run time issues of the select method; however, editing the 
code would take longer the than improvement in run time on most of my 
macros.  I will keep this in mind on the macros I use more than 25 times, 
but typically I write macros for a project to update multiple workbooks 
after the project has started.  I typically have less than 20 workbooks on 
which to run the macro, but want to be sure that I run it on the correct 
sheet.

I also run the macro once on the workbooks I use as templates so I don't 
need to run the macros in future projects.  In other words, most of my 
macros are made by using the recorder, edited as needed, and then deleted at 
the end of the day.  Editing to avoid the select method would not be 
efficient for most of my macros, but I will review at the ones I keep long 
term.

Thank you again.  It was helpful.

Brad

Excel 2002 on XP Pro SP 3
Excel 2007 on Vista 64
"joel" <joel.44sghz@thecodecage.com> wrote in message 
news:joel.44sghz@thecodecage.com...
>
> The answer is "NOT TO USE the SELECT METHOD".  In your code reference
> each sheet by its object or Name.  Don't use recorded macros without
> eliminating all the select properties and activate properties (there are
> only a few cases where this may not work).
>
>
> Selection of TABS slows down the macro and can create errors like you
> are finding out.  Here are some examples of the correct method of
> referencing objects
>
>
> set bk = workbooks.open(Filename:=Book1.xls)
>
> set sht = bk.sheets("sheet1")
>
> for each sht in bk.sheets
>
>
> set DataRange = sht.rangge("A1:B100")
>
>
> -- 
> joel
> ------------------------------------------------------------------------
> joel's Profile: 229
> View this thread: 
> http://www.thecodecage.com/forumz/showthread.php?t=170049
>
> [url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]
> 


0
Brad
1/14/2010 5:51:51 PM
Joel H,

Thank you for your help.  It gave me what I was looking for.

Sorry for the confusion with the terminology.  Yes, when I said tab I meant 
sheet and when I said spreadsheet I meant workbook.  I will try to be 
clearer in the future.  While looping and including the workbook names would 
reduce the number of times I need to run the macros, it would take longer to 
code in the looping than to just manually select the workbook and run the 
macro 12 to 15 times.

I use Outlook Express to access the discussion group, so I do not see how to 
check the "YES" option from here.

Thank you again.  It was helpful.

Brad

Excel 2002 on XP Pro SP 3
Excel 2007 on Vista 64
"Ryan H" <RyanH@discussions.microsoft.com> wrote in message 
news:ECD99AA4-5B6E-4303-9913-55B05F72D548@microsoft.com...
> Your post is kinda vague, plus you didn't post any of your current code.
> When you say "spreadsheets" do you mean workbooks?  And when you say 
> "tabs" I
> assume you mean worksheets within the workbook, right?  But I think you 
> want
> to activate a sheet in a particular workbook before a particular macro is
> ran, right?  If so, you can use this code below.  It sounds to me you need 
> a
> loop referencing workbooks and sheets, which would be more efficient. 
> Hope
> this helps!  If so, let me know, click "YES" below.
>
> Sub YourSub()
>
>   Workbooks("Book1.xls").Sheets("Sheet1").Activate
>
>   ' your code for that worksheet here
>
>   Workbooks("Book1.xls").Sheets("Sheet2").Activate
>
>   ' your code for that worksheet here
>   'etc
>
> End Sub
> -- 
> Cheers,
> Ryan
>
>
> "Brad" wrote:
>
>> I have lots of spreadsheets that have the same seven tabs in them.  How 
>> do I
>> automatically ensure that the correct tab is selected when running a 
>> macro
>> with absolute references?  Is there a way to have the macro select the 
>> tab
>> before running the rest of the macro?
>>
>> Brad
>>
>> Excel 2002 on XP Pro SP 3
>> Excel 2007 on Vista 64
>>
>>
>> .
>> 


0
Brad
1/14/2010 5:54:18 PM
Reply:

Similar Artilces:

How can you define Page name as a function of other variables.
I'm using visio 2002. I am able to include the value of the "Page name" to define anothe variable, simply by referencing "pagename()" in the formula. For example:thetext=pagename()&"hello" However, is it possible to define the "Page name" as a function o other values on the sheet? For example: Could I define the page name in this fashion : Pagename()= "hello"&shapetext(bluebox.39!thetext) where "bluebox.39" is a shape on the sheet. If so where do I do this..? I can't find the pagename cell on the pag shapeshee...

Tab in text box
Hi, I have a form with 11 text boxes. If I tab, tab, tab.... through the boxes after the last box my data in all the boxes goes blank. Can someone tell me why and how to fix this problem? Thanks Phil "Phillip" <Phillip@discussions.microsoft.com> wrote in message news:9B22781F-1C27-47E6-8320-4E3423BA6218@microsoft.com... > Hi, > I have a form with 11 text boxes. If I tab, tab, tab.... through the > boxes > after the last box my data in all the boxes goes blank. Can someone tell > me > why and how to fix this problem? It sounds to me ...

Getting the values in a named range
Hi, I want to list, in the inmediate windows, the values of the named ranges in my workbook. I prefer, if possible, not having to specify the worksheet names. I've tried the following code: Sub abc() Dim a For Each n In Names Debug.Print n, n.Name a = n.Value Debug.Print a Next End Sub I get: =sdf!$D$6:$D$8 casa =sdf!$D$6:$D$8 =sdf!$C$12:$D$14 mesa =sdf!$C$12:$D$14 however I would like something like =sdf!$D$6:$D$8 casa 1 2 3 =sdf!$C$12:$D$14 mesa 7 8 9 is this possible? Thank you Na...

How can I add a recurring footer (name/address) to new messages?
We recently received an email from a customer with their company name and address centered at the bottom of the page as you would have on company stationery. How can we implement this in Outlook for our emails? Click the "Center" button in the Signature dialog box after selecting the text. Cyn @ Spectrum wrote: > We recently received an email from a customer with their company name and > address centered at the bottom of the page as you would have on company > stationery. How can we implement this in Outlook for our emails? ...

Writing XML file from dataset without DS name
I have an XML file from a client that I generated a schema from. I Open a DataSet and read the schema to get the tables and relationships. I write my tables and fields and assign parent rows etc. When I write the data, the output file has a new wrapper element with the name of dataset. Is there a way to write the XML from the dataset without this wrapper? I also need to provide the encoding attribute like the input file. Example input XML <?xml version="1.0" encoding="ISO-8859-1"?> <Customer Name="XXX" Trigram="XXX"> <InputFil...

how to combine several tabs on a spreadsheet into a summary tab w.
I have a spreadsheet with several tabs containing contact information by city ( each tab represent a city) I now would like to combine them into a summary Tab containing all the information. Copy and paste, perhaps? On Sat, 4 Dec 2004 19:15:03 -0800, "adeeb" <adeeb@discussions.microsoft.com> wrote: >I have a spreadsheet with several tabs containing contact information by city >( each tab represent a city) I now would like to combine them into a summary >Tab containing all the information. Hi adeeb Here is a macro solution http://www.rondebruin.nl/copy2.htm -...

'struct' type redefinition error while including <winioctl.h>
Hi i am facing a problem while getting hard disk information. when i use in New MFC exe project it works well. But i when i use this code in my original project which is also MFC Dialog bbased application with these liberaries included Ws2_32.lib Winmm.lib Vfw32.lib the compiler gives 'struct' type redefinition error. When i comment <winioctl.h> it give different errors but errors but not the earlier one. Could anybody please help me out. Thanks in advance... Mujtaba Why you think the sequence of lib files to the linker should have any impact on the compiler escapes me...

Clearing Automatic Name Checking/Suggestion
Does anyone know how to clear the memory setting for automatic name checking? When it suggests an email address, it gives me an old one or incorrect one from my outlook that is no longer in my Contacts and it's frustrating. Please advise, I've already tried turning the settings on and off with no luck. Thanks, KjL ...

Include Dropdowns and Checkboxes in Worksheet Change event
I am tracking the changes made to cells in the spreadsheet and that is working. However, there are three active X dropdown lists objects and numerous check boxes that are not trapped. Each is linked to a cell on the same sheet in a hidden area that is updated when their values change. I have code to trap the cells that are changed and execute additional code. The code in the worksheet change event is; Private Sub Worksheet_Change(ByVal Target As Range) Dim tCell As Range Call ChangeOrder(tCell, tSheet) In the worksheet module is this code; Sub ChangeOrder(tCell As Rang...

Named Ranges in Formulas
I have a worksheet with four columns - Total Hours Percent to total Hours Value of Hours Percent to total Value of Hours The row with the total varies, so in my macro I have named the row with the total TOT_HOURS (=a and whatever the last row is) In column B, on each row, I perform the calc b1/tot_hours, etc., to get the percent to total. How could I make tot_hours relational, that is, when I copy the formula to column d, have tot_hours refer to column c instead of column a? Just copying the formulas doesn't work. more detail and sample formulas -- Don Guillett SalesAid Soft...

refer
Hi. Please help me. I need to select a range based on a start location that's diferent sheet by sheet. I tried but i couldn't make it. The start position it's variable depends on that i have select cell, and the final position is U3000. I want select this range and them delete all the rows of the range. Rows(ActiveCell.select:u3000).Select If you really want to delete the rows, one way: Range(ActiveCell.Row & ":3000").Delete If you just want to delete the cells in the range from ActiveCell to U3000, one way: Range(ActiveCell, Cells(3000,21)).Dele...

Email Display Name As
Using 2003 with BCM V2... Sometimes I have the hardest time changing an email so that it displays correctly. Usually, it will add the persons name automatically, but sometimes it won't. I change an email/contact person by 1) change the contact name in the contact record 2) double click on the email 3) change the email 4) click OK Sometimes the Display as has the person name from the contact field, other times it is the email only, with NO way to correct it. Any ideas? Bob Day Your post probably makes sense to you because you know what you mean. No one else could, however. To ...

"You cannot open two documents with the same name" message problem
Hi All, I am wondering if anyone has a solution on this. There is a Word 2007 file (docx) that has links to an Excel file. They are both resided in the same folder. When the files are moved to another folder, the link path does not get updated =96 which I understand. But when the Word file is first opened, the following message came up: A document with the name "xxx.xlsm" is already open. You cannot open two documents with the same name, even if the documents are in different folders. To open the second documents, either close the document that's currently open...

Force outlook to go to AD to get Exchange server name
I've this quesitons about Microsoft Outlook 2000/2003 clients (MAPI) for Exchange 2000 and 2003 servers. How can I force the Outlook clients to go to AD to find out which exchange server is holding the mailbox of the user? I want to do that every time clients starts or refresh it if it is already running. This way I don't have to reconfigure (or "Check Name") them whenever I move mailbox of the user from one exchange server to another in the organization. Thanks RD You shouldn't have to do that. When Outlook connects, it should get the Home Server attribute f...

How do I export names from a distribution lists
I would like to copy the names from my distribution lists in outlook to another program so I don't have to retype them all. What version of Outlook? >-----Original Message----- >I would like to copy the names from my distribution lists in outlook to >another program so I don't have to retype them all. >. > I am using Outlook 2000 "Chuck Davis" wrote: > What version of Outlook? > >-----Original Message----- > >I would like to copy the names from my distribution lists > in outlook to > >another program so I don't have to retype ...

How do I refer to a cell from header
How can I customize the header to refer to a specific cell in th worksheet -- isto ----------------------------------------------------------------------- istor's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1576 View this thread: http://www.excelforum.com/showthread.php?threadid=27273 Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim sText As String With ActiveSheet .PageSetup.LeftHeader = .Range("A1").Value End With End Sub -- HTH RP "istor" <istor.1es4tz@excelforum-nospam.com> wrote in message news:ist...

Worksheet name variables
I have a project that requires me to copy mutiple worksheets to one hub worksheet. Is there a way to make the worksheet name a variable? This is what i have so far: =IF(Period8224a!$R$9=$C$2,Period8224a!$N$9,0) The Period8224a name will change like 50 to 100 times. If there was a way to reference the name of the sheet from a cell on the hub sheet like this: =IF("X1"!$R$9=$C$2,"X1"!$N$9,0) with the "X1" cell containing the worksheet name. You can use the “Indirect” function to get the name of the worksheet into a formula. Assume the name of ...

OWA Find Names
When using OWA, you look up recipients for an e-mail using the "Find Names" window. It appears that the maximum number of results that will be returned is set at 100, with no customization obviously apparent from the OWA interface. I completely understand why you wouldn't want this number to get too high, especially in a large organization, but I have a need to increase it to at least 200, if not 250. Is there registry key where this can be controlled? Perhaps a way to set something in the IIS metabase? Any help is greatly appreciated. On Mon, 6 Feb 2006 13:34:17 -080...

Matching names
Please help! I work with large Excel spreadsheets with tons of columns of data involving hospital inpatient census. I have been trying to find a way to write in VBA a way to match the names of hospitals in eg. column C with their nurses in eg column G. I can autofilter the names, etc, but can't figure out how to match up all the hospitals with name MEDCENTRAL with nurse JANE. Is this an IF/THEN function? If so, I can't get the syntax right. My main problem seems to be isolating only the visible part of column G when I autofilter and select the nurse name. Is there a funct...

Making Name Tags
I am tasked in making Name Tags(Labels) from the Names from my database. I have created a query of the just the [First Name] and [Last Name] of all needed employee names. I am not quite sure where to go from here, so an guidance will be appreciated. Eric In Access2003 you have a reportwizard which includes a wizard to create address labels (make sure the reports 'tab' is visible and click 'New'. Maybe you can start there and use your query. Lars "Eric Starn" <EricStarn@discussions.microsoft.com> schreef in bericht news:55845B3C-249A-47B...

sorting customer name and IP with value
any script to sort following list? i have the following IP address, customer name and value A B C IP address customer name value 10.10.10.1 Lee 10 10.10.10.2 wong 30 10.10.10.3 chan 40 10.10.10.4 chan 15 10.10.10.9 wong 16 10.10.10.11 Lee ...

OWA check names search limits
Does anyone know if the 100 search item limit can be increased for OWA check names. I know that you can customise standard searches up to 1000 items but the check names appears to be different. any ideas? ...

Exchange 2003
Hello All, Yesterday night we started receiving lots of errors on our Exchange 2003 SP1 server (Exchange is in native mode). Server reports that there are constant attempts to create named properties, so that the quota (36K) had been exceeded. Here's the typical error message: 1) Event ID 9667, source: MSExchangeIS, Category: General Failed to create a new named property for database "First Storage Group\Mailbox Store (MVEXCH1)" because the number of named properties reached the quota limit (16384). User attempting to create the named property: "BESAdmin" Named prope...

distribution list - hide names
What is the BEST way to send the same email message to a large group of customers, but control what the recipient sees in the "To:" field of the email they receive? Sample distribution list "My Customers" Miles Smith (msmith@abbott.com) Michael Dell (mdell@dell.com) Steve Balmer (sbalmer@microsoft.com) I know by default that when I choose a distribution list such as "My Customers" in the "To" field when I create the message, it looks like To: + My Customers on my screen, but when the email is received by each person, their email looks like "T...

Can't change a cell name back
I had a cell named "MP2OZ". It was supposed to be "ML2OZ" for milliliters to ounces. When I noticed the typo, I selected the cell, then clicked in the Name Box, and typed the correct name. As soon as I did it, I realized that this would not change the formulas that use that name. So, I changed it back (to MP2OZ) and then opened up the Name Manager (Ctrl+F3) and tried to change MP2OZ to ML2OZ. The Name Manager says that both the old and new names are already in use and to choose a different name. But the Name Manager only shows the one name (MP2OZ). It does not show ML2OZ....