Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Including tab name in absolute reference
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/sh...d.php?t=170049 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Including tab name in absolute reference
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Including tab name in absolute reference
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" wrote in message ... 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/sh...d.php?t=170049 Microsoft Office Help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Including tab name in absolute reference
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" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Absolute cell reference will not remain absolute. | Excel Worksheet Functions | |||
How can I do an Absolute Cell Reference but non Absolute Cell Pais | Excel Programming | |||
How can I do an Absolute Cell Reference but non Absolute Cell Pais | Excel Programming | |||
Creating absolute references including active sheet name in the formula | Excel Programming |