Daniel has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Excel VBA Advanced course
If Worksheet name contains X then do Y
Hi,
I'm trying to write a procedure that will consolidate the information from a number sheets. The sheets are all within the same workbook and there is a mix of names. However the sheets I want to consolidate all start with the word holdings. How can I write some code which has a loop which looks at the worksheet name, determines if it starts with holdings, if it does then copies the data to a master sheet and if not moves onto the next sheet?
Any help would be much appreciated.
Many thanks.
RE: If Worksheet name contains X then do Y
Hi Daniel
One way to copy only for the holding sheets is to use an IF statement such as:
If Left(mysheet.Name, 8) = "holdings" Then
Cells.Select
Selection.Copy
...
End if
The Left function picks up the first 8 characters of the sheet name.
This would be within a For loop that cycles through all the sheets adding the data to the master sheet.
I've attached an example where 3 holding sheets are consolidated with Copy/Paste Special Add.
You didn't say how your macro did the consolidation but let me know if I'm on the right lines.
Thanks
Doug Dunn
Best STL Training
Consolidate holdings.xls
