Look Who it Was Random video

Excel - Search All Worksheets in a Workbook with a VBA Hack - Episode 957

590 views · Published 24 February 2009 · 2:47 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Search All Worksheets in a Workbook

Welcome back to the MrExcel netcast! In this episode, we're going to revisit a topic from episode 954 - searching all worksheets in a workbook. This topic generated a lot of mail and I want to thank everyone who wrote in with their solutions and suggestions. We'll also be addressing a question from "Excel is fun" Mike Gel Girvin on YouTube about using SendKeys in macros.

For those of you who are still using Excel 2000, we'll also cover how to search all worksheets in a workbook using the "Find" command.

Now, back to the topic of searching all worksheets in a workbook. As some of you pointed out, this can be done by changing the options in the "Find" command. However, for those using older versions of Excel, this may not be an option. That's where the SendKeys function comes in handy. But thanks to Mike's question, we'll also show you how to use the application.Dialogs.show function to bring up the "Find" dialog box without using SendKeys.

But wait, there's more! We'll also be discussing another way to solve the problem of searching all worksheets in a workbook - using the formula find and formula replace functions. These can be accessed through the application.Dialogs.show function and are a more efficient way to search for specific data in your workbook.

So, if you are trying to search all worksheets in a workbook, this episode has something for you. Thank you to everyone who wrote in with their questions and suggestions, and a special shoutout to Laura in Waukesha for her question on using the "Find" command in Excel 2000. As always, thank you for tuning in to the MrExcel netcast and we'll see you next time for more Excel tips and tricks!"

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Episode 954 brought an avalanche of mail on three fronts. (1) SendKeys is really cool, how do you do Alt instead of Ctrl? (2) Why the macro when you could select Entire Workbook in the dialog? (3) Application.Dialogs would have worked, the dialog is misnamed. Episode 957 discusses all of these tips.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Table of Contents:
(00:00) Solving a problem with searching worksheets
(00:13) Using the "Find" command to search entire workbook
(00:23) Needing to search all worksheets in Excel 2000
(00:33) Improvements in the "Find" box in newer versions of Excel
(00:50) Sending special keys with macros
(01:00) Using SendKeys to send "Alt" key
(01:12) Sending shift key with macros
(01:22) Using application.Dialogs.show to find and replace formulas
(01:43) Clarification on finding and replacing formulas
(01:53) Using formula find and formula replace in application.Dialogs.show
(02:25) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Application.Dialogs.show method
Control L shortcut
Excel 2000 limitations
Find dialog boxes
Formula find and replace
MrExcel netcast
Problem-solving in Excel
Searching worksheets in a workbook
Sending Alt key (using %)
Sending Shift key (using +)
Using SendKeys in macros

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152248/

More from this channel