Activate Sheet using Partial Sheet name

[grazian2]

Hello!

I have searched and searched for a previous response to this question to no avail.

I am writing a VBA program in Excel 2010 (Windows) that is going to iterate through several workbook, copy data, and paste said data into another workbook.

The issue is that each of the workbooks I am copying data from do not have the exact same sheet names.

For example, Workbook A has a sheet named "Mean_xxx_xxxx", Workbook B has a sheet named "xxx_mean_xxxx", and Workbook "C" has a sheet named "xxx_xxxxxxxx_Mean". I want to copy data from each of those workbooks and paste it into workbook "D".

As can be seen, each workbook's sheet that I want has the string "Mean" in it.

Is it possible to activate wach workbook's sheet using only the partial string of each sheet?

Any help would be greatly appreciated!

Sample Code:

Please Login or Register  to view this content.

Instead of writing the specific sheet name, as in the code above, can I seach for the partial string "mean" inside the full sheet names?


Thanks,

Grazian2

Excel 2010

[nathansav]

Yes, you can loop the sheets and use like "*mean*" etc

[Norie]

Short answer, no you can't activate a sheet with the partial name.

What you could do is loop through the worksheets in a workbook to find the one you want using a partial match.

[grazian2]

Thank you for the responses!

@Nathansav: What would that look like?

Please Login or Register  to view this content.

Is that what you mean?

[grazian2]

@Norie:

Would I use the Find function to seach for the string inside of the sheet name, or did you have a more effective idea?

[berlan]

hrazian2, here's an example (untested):

Please Login or Register  to view this content.

Cheers, berlan

[Norie]

This code will find the first sheet with 'mean' in it's name in all 3 workbooks.

Please Login or Register  to view this content.

[grazian2]

Thank you for the help, those both worked great!