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

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

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


.

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

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


.