VBA - How to select a range in a non-activated worksheet :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

VBA - How to select a range in a non-activated worksheet
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

MrDoc
Board Master


Joined: 31 Jul 2002
Posts: 221
Location: Portugal
Flag: Portugal

Status: Offline

 Reply with quote  

VBA - How to select a range in a non-activated worksheet

Hello,

I need help on a very simple matter (I think...):
When I try to use

Sheets("Dir").Range("B2:B10").Select

in VBA code, there is an error "Select method of range class failed",
and I have to use

sheets("Dir").activate
range(B2:B10").select

What am I doing wrong?
I really donīt want to activate the "Dir" worksheet, just select a range on it and work on that range - sort it, for instance.

Thank you for any help.
Best Regards,
MrDoc

Post Sun Sep 21, 2003 12:54 am 
 View user's profile Send private message

tbardoni
Board Master
Board  Master


Joined: 30 Aug 2002
Posts: 1613
Location: Rochester, MI USA
Flag: Usa

Status: Offline

 Reply with quote  

Re: VBA - How to select a range in a non-activated worksheet

You're not doing anything wrong. You MUST activate the sheet before selecting a range on that sheet.

What exactly are you trying to do and why do you not want to activate the sheet?
_________________
Board Nit
Dyslexics of the World Untie!

Post Sun Sep 21, 2003 12:57 am 
 View user's profile Send private message Send e-mail AIM Address MSN Messenger

MrDoc
Board Master


Joined: 31 Jul 2002
Posts: 221
Location: Portugal
Flag: Portugal

Status: Offline

 Reply with quote  

Re: VBA - How to select a range in a non-activated worksheet

Thank you for your reply. I'm sorting a range in "Dir" worksheet, and then copying part of it to "TronX" worksheet, where X is the highest value in the existing Tron1, Tron2, TronN....worksheets. So I'm not using sheets.count, because it depends on the sheet tab position.
Now, having to activate a sheet and then the other, and repeating it again and again, becomes somehow confusing, mainly because I'm editing code I wrote a long time ago.
But maybe I can sort a range in "Dir" worksheet without selecting it??
Best Regards,
MrDoc

Post Sun Sep 21, 2003 1:09 am 
 View user's profile Send private message

DRJ
Board Master
Board  Master


Joined: 18 Feb 2002
Posts: 1022
Location: California
Flag: Usa

Status: Offline

 Reply with quote  

Re: VBA - How to select a range in a non-activated worksheet

In most cases you will need to activate the sheet you want to work with first. If you do not want events to trigger because you activated the sheet, or deactivated the current sheet you can use (application.enableevents=false) to turn off triggered events. Just remember to turn them back on later. If you do not want the user to see the sheets changing as different sheets are activated use (application.screenupdating=false) to stop the screen from updating until the code is finished running.

If this doesnt help post more info on what you are trying to do.

Post Sun Sep 21, 2003 1:12 am 
 View user's profile Send private message Send e-mail

Tom Urtis
MrExcel MVP


Joined: 11 Feb 2002
Posts: 2278
Location: San Francisco / Silicon Valley, California USA
Flag: Blank

Status: Offline

 Reply with quote  

Re: VBA - How to select a range in a non-activated worksheet

You don't need to select / activate a sheet in order to sort a range on it.

Based on your posted example, one syntax could be:

With Sheets("Dir")
.Range("B1:B10").Sort Key1:=.Range("B2"), Order1:=xlAscending, Header:=xlYes
End With

Assuming B1 contains a header value.
_________________
Tom Urtis

Post Sun Sep 21, 2003 3:39 am 
 View user's profile Send private message Send e-mail Visit poster's website

MrDoc
Board Master


Joined: 31 Jul 2002
Posts: 221
Location: Portugal
Flag: Portugal

Status: Offline

 Reply with quote  

Re: VBA - How to select a range in a non-activated worksheet

Thank you all for helping. I think Tom's reply is just what I was looking for in this particular case - not having to activate sheet1 and then sheet2 and then sheet1 and... in my code. I'll try it as soon as I can. I really expected that to be possible, since it's also possible to change cell values using VBA even if the worksheet is hidden, but I couldnīt figure out how to do it.
Best Regards,
MrDoc

Post Sun Sep 21, 2003 5:49 pm 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.