Altering VBA find function to search entire workbook

**zaclim78** · 09-19-2012, 03:40 PM

Hi everyone,

The code below is for a button which will search a worksheet (.find function) to find a value that has been typed into the textboxes on the form. It subsequently copies the entire row of any hits and pastes them into a new sheet.

I would like to alter the code so that it searches ALL the sheets within the workbook.

I would really appreciate any help!

Please Login or Register  to view this content.

**Tinbendr** · 09-19-2012, 05:29 PM

try this.

Please Login or Register  to view this content.

**zaclim78** · 09-20-2012, 02:34 PM

Hi David,

Thanks for the quick reply. I get a compile error: Next without For

**Oppressed1** · 09-20-2012, 03:24 PM

Since I know what it's like to wait, to give you an answer, and not meaning to step on Tinbendr ...

What he's doing is wrapping your code inside a loop that executes the loop's contents for each worksheet. The key lines are

Dim ws As Worksheet

and

For Each ws In Worksheets
With ws
ws.Unprotect

and finally

next ws

I haven't checked, but either he just left off the last one, or the "next" at that point connects to some other "for." Also note that the "With" can be further capitalized on; what he shows leaves the prefix on.

He has two more lines
ws.Protect userinterfaceonly:=True
ws.EnableSelection = xlNoRestrictions
which you would have to decide to use or not. As that goes, the .unprotect might be debated as well.

HTH. Is this a class assignment?