Excel VBA Basics #30 How to Loop Through Each Worksheet in Workbook

This tutorial shows you how to create a macro that can easily affect each sheet instantly. It cycles through them one at a time, so you can do commands.

The basis of this is a simple "For Each" loop:

For Each ws in Thisworkbook.Sheets
'your code here
Next ws

Fantastic Developer Tools:

[MYJETGLO]

Hello Danny, Been going through your videos for the past few weeks and I must say they are superb!!! The difference that both you and Mike (excel is fun) bring is the intensity of explaination and detail, an aspect which so many have failed to employ in countless previous and current courses and videos. Both you and Mike have found the secret to developing sucessful excel courses so please maintain this excellent standard. Thank you for everything you have

[strawdog]

hi Dan,
I just wanted to say thanks for your excellent videos. The amount of detail and explanation you use make these videos top notch. I am new to all this and am having a hard time with a loop that will bring back specific cells from multiple worksheets. This workbook would contain 185 worksheets and what I need from each worksheet is
to find a specific value 'JAX' at row 5 and column 6 and then return a value from the same row 5 but column 8. For some reason I cannot figure this out.
Thxs

[bobkaplan]

Explained very succintly and easy to understand!  THANKS!

[ExcelVbaIsFun]

Merry Christmas everyone. I hope God blesses you richly this upcoming year. Remember, you ARE a success!! Believe it, be it! Blessings my friends!! Dan

[navuamarai]

Hey buddy, thanks for the videos. They are really helpful. Keep up the good work.

[nicholestatum]

Thank You are fantastic! I literally had an ahha moment.

[richardmonti]

Great and extremely helpful video! Thank you

[ThatYooChoobGuy]

Hey great videos mate, well done. I've been using Excel for years, but have never been able to get my head around VBA - these videos are really helping! Cheers

I got a quick question for you too; I often name ranges in excel using symbols as it's more concise and descriptive; for example I might name cell A4 as "∑M". I'd like to reference this named range in VBA, but it doesn't seem to accept symbols - is there any way around this, or must I refrain from using symbols in named ranges?

[ExcelVbaIsFun]

Hello ThatYooChoobGuy! Great questions! I believe that for best results with the limited ANSI characters (255), named ranges must be numbers, letters or underscores - and that's about it unfortunately. I didn't know that it would allow you to use such symbols, like in your example, but I wouldn't. Also, you can't name a range that already has a name, such as A1, or Q50. Sorry sir.

thank you so much, i am a beginer, , , feel so helpfull.

[saadali-ftsl]

thanks a lot for all videos .why don't you have any access vba lessons? i ask this question because your lessons in excel are very benefit so i hope to find same type of access vba

[EdwardLeeDesign]

Excellent! So how would I copy each worksheet to a separate workbook (master), to specific worksheet names within that workbook (master) ?

[jmcrapo]

I got one for you


Sub fillbblanks()

Dim singlesheet As Worksheet

For Each singlesheet In ThisWorkbook.Sheets


range(Selection,
range(Selection, Selection.End(xlDown)).Select
On Error GoTo NoBlanks

Selection.Value = "n/a"

NoBlanks:
range("A1").Select

Next singlesheet

the problem is that every time I step through this routine it never goes to the next worksheet

[LongTimeTTFan]

Hello, Dan. Thank you for your series of videos. You explain the steps very clearly. I am learning VBA by watching your video lessons.
A quick question. How can i loop through all the 30 worksheets in my workbook to perform the same procedure: getting the value in a PREVIOUS worksheet cell, say C5, to cell C4 in the immediate FOLLOWING worksheet? (For example, if C5 in Sheet1 has a value of 100, automatically, C4 in Sheet2 will have that value of 100. And this applies to the following worksheets, C4 in Sheet3 will get the value of C5 in Sheet2. etc? Any help will be appreciated.

[thomashaynie]

I need this as the base for an action but I ned it to skip the first tab. Could you illustrate?

[Tat-cxlr]

Bonjour
Merci pour votre partage
Ma macro ne fonctionne pas au niveau de copy:=
Pourriez-vous svp m’aider
Fred

[edmerzlak]

How do you do this using sheet code-names so the end user cannot cause it to crash?

[cedricrwhite]

What is the difference between ThisWorkbook.Sheets and ThisWorkbook.Worksheets? I am attempting to use your code as a template but I either get mismatch error (13) or the macro does not cycle through all of the worksheets.

[jhenry]

ExcelVbaIsFunWhat do you write in place of the for statement, if you want to only run the script on say, sheets, 1, 3, 5 and 9 or say you only want to run the script on sheets 2, 4, 6, 8 ie even numbers, etc. how do you specify which sheets to run it on ?

[preludefugue]

Hi Dan, so if I want to start the loop function from the 2nd sheet (count from left) and loop the entire book until it reaches no sheet.... how to do this???   Thank you.