worksheet looping with varying labels (aka Need to Find the John!)

[alphapoint05]

Here's the problem:

Suppose a macro creates a certain number of worksheets (which varies
from dataset to dataset). Further suppose that text labels (in the
tabs) are created by the macro for each worksheet (that also vary from
workbook to workbook).

Now, I want the macro to go through the worksheets and pick out the
ones that have particular text.

Example:

Macro creates 6 worksheets for this dataset:

John - 1
John-2
Mary -1
Mary-2
Fred
Debbie

I want to find the John's.

I envision a loop where it finds the first John (John - 1) and then
executes the appropriate actions on this worksheet. It then finds the
second John (John-2) and executes those same actions. I can do this,
but I can't get it to loop through the worksheets and pick these
particular worksheets.

Anyone have any ideas on how to find these John's

Jeff Miller

[Cody]

Worksheets are a collection. Use a for each loop to search through the
worksheets in the workbook. Use an if statement if you find what you are
looking for then perform the action necessary.


"alphapoint05" wrote:

> Here's the problem:
>
> Suppose a macro creates a certain number of worksheets (which varies
> from dataset to dataset). Further suppose that text labels (in the
> tabs) are created by the macro for each worksheet (that also vary from
> workbook to workbook).
>
> Now, I want the macro to go through the worksheets and pick out the
> ones that have particular text.
>
> Example:
>
> Macro creates 6 worksheets for this dataset:
>
> John - 1
> John-2
> Mary -1
> Mary-2
> Fred
> Debbie
>
> I want to find the John's.
>
> I envision a loop where it finds the first John (John - 1) and then
> executes the appropriate actions on this worksheet. It then finds the
> second John (John-2) and executes those same actions. I can do this,
> but I can't get it to loop through the worksheets and pick these
> particular worksheets.
>
> Anyone have any ideas on how to find these John's
>
> Jeff Miller
>
>

[Rowan]

Hi Jeff

you can loop through the worksheets collection like this:

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If Left(UCase(ws.Name), 4) = "JOHN" Then
'do sheet stuff
End If
Next ws


Hope this helps
Rowan

alphapoint05 wrote:
> Here's the problem:
>
> Suppose a macro creates a certain number of worksheets (which varies
> from dataset to dataset). Further suppose that text labels (in the
> tabs) are created by the macro for each worksheet (that also vary from
> workbook to workbook).
>
> Now, I want the macro to go through the worksheets and pick out the
> ones that have particular text.
>
> Example:
>
> Macro creates 6 worksheets for this dataset:
>
> John - 1
> John-2
> Mary -1
> Mary-2
> Fred
> Debbie
>
> I want to find the John's.
>
> I envision a loop where it finds the first John (John - 1) and then
> executes the appropriate actions on this worksheet. It then finds the
> second John (John-2) and executes those same actions. I can do this,
> but I can't get it to loop through the worksheets and pick these
> particular worksheets.
>
> Anyone have any ideas on how to find these John's
>
> Jeff Miller
>

[alphapoint05]

What if I'm not looking for the entire text of the tab but just one
word within it

Jeff

[alphapoint05]

Thank you very much! This worked well. I had to make one change....had
to remove UCase due to the nature of my names.

Thanks again,
Jeff

[Rowan]

Glad it helped. The UCASE is there (matched with a search for "JOHN") so
that it does not matter how the names are entered. If you only want to
change sheets where the name is entered with a specific case then remove
the UCASE.

Regards
Rowan

alphapoint05 wrote:
> Thank you very much! This worked well. I had to make one change....had
> to remove UCase due to the nature of my names.
>
> Thanks again,
> Jeff
>