Object does not support property or method ForEach Loop

Question

Hi I'm trying to iterate through worksheets of a workbook using a For Each loop. My code is pretty simple:

Dim visualsBook As Workbook
Set visualsBook = ThisWorkbook
For Each ws In visualsBook
   /action
Next

However I keep getting an "Object does not support property or method" error. Does anyone know why this is happening?

Try For Each ws In visualsBook.Worksheets assuming that you declared ws As Worksheet — arcadeprecinct
– arcadeprecinct, Commented Sep 23, 2016 at 12:42
@arcadeprecinct: That is a valid answer. You may want to put it down as such? :) — Siddharth Rout
– Siddharth Rout, Commented Sep 23, 2016 at 12:51
I don't understand why everyone puts answers in comments on this forum. Is there some real reason or is it just habit? — jamheadart
– jamheadart, Commented Sep 23, 2016 at 12:53
it's just a habit for answers that are really short (for me). I'll write up an answer. — arcadeprecinct
– arcadeprecinct, Commented Sep 23, 2016 at 12:53
@jamheadart: Is there some real reason or is it just habit? None of those reasons actually. Sometimes I also do it. I don't know about others, but I do it because of sheer laziness :P... And sometimes because the answer is so obvious that I do not want to write a good lenghty quality post for it ;) — Siddharth Rout
– Siddharth Rout, Commented Sep 23, 2016 at 12:59

arcadeprecinct · Accepted Answer · 2016-09-23 13:08:26Z

3

A workbook is not a collection that you can loop through. Use visualsBook.Worksheets instead.

For Each ws In visualsBook.Worksheets
   /action
Next

edit: visualsBook.Sheets contains charts as well as worksheets so better use .Worksheets

answered Sep 23, 2016 at 12:58

arcadeprecinct

3,7771 gold badge14 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

You can also use .sheets instead of .worksheets if you like to save a nearly infinite amount of time cutting out four letters :D

@jamheadart: I hope you are aware of the difference between sheets and worksheets? :)

@SiddharthRout se, this is why I only wrote a comment, now I had to learn something new!

@SiddharthRout Actually I'm not really, I'm guessing using .sheets isn't always going to produce the same result as .worksheets then :/

a worksheet is a sheet that works hard duh... alright sorry, but thanks guys that was the problem.