Index/Match range inside of For Each Selected worksheet

set wb = workbooks.open(myFile)
for each wksht in wb.Worksheets
  for i = 2 to myCount
    wksht.Range("C1:T5000") ' This will actually be an index/match
  next
next

Open in new window


Trying to figure out how to select a range inside of the for each loop of a set of worksheets. Need this for an index/match function that I'm creating, but I'm getting an "Object doesn't support this property or method" error.

Thanks!
keats88Asked:
Who is Participating?
 
TommySzalapskiConnect With a Mentor Commented:
You cannot select a range on an inactive sheet. If you really want to select it you must to wksht.Activate first.
0
 
rspahitzCommented:
Did you try using the select method?

wksht.Range("C1:T5000").Select

Of course, after you select, you'll need to do something with Selection or it will be a useless task.
0
 
TommySzalapskiCommented:
You might need to activate the workbook too.

However, it is good practice to avoid selecting if you can (and you usually can).
0
 
rspahitzCommented:
Agree with Tommy about selecting.  You can simply reference any cell you want with the Cells method, then the cells are not selected but you can read/write anything you want to them, even on other sheets.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.