Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » If Worksheet name contains X then do Y
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
If Worksheet name contains X then do Y
Resolved · Medium Priority · Version 2010
Reply Wed 8 Feb 2012, 22:16Delegate Daniel said...
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.
For upcoming training course dates see: Pricing & availability
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
Attached files...
Consolidate holdings.xlsFri 17 Feb 2012: Automatically marked as resolved.
|