...If Worksheet name contains a certain word...

[Lenny Enfield]

Hello,

I am trying to create a macro to run in a document that contains many sheets. There is one sheet at the beginning, and then subsequent sheets which all have a calendar month in the title.

What I want the macro to do is recognise which 'month' it is in a paste a value into its respective cell on the first sheet.

My macro at the moment is:

Sub Macro1()

Range("D4").Select
Selection.End(xlDown).Select
Selection.Copy
If ActiveSheet.Name = ("IR_25th November 11") Then
Sheets("Sheet1").Select
Range("L3").Select
End If
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

So at the moment when I run macro in the "IR_25th November 11" sheet it copies the piece of data I need into the correct cell on the front page. What I would like it to do is to run just as long the worksheet has the word November in the title. Is this possible?

Please let me know if any clarification is required as I don't think I have explained this very well!

 

[gsbelbin]

I think I'm understanding what you want but perhaps not

If you just want to check if November is in the sheet name you could use

If InStr(1, ActiveSheet.Name, "November", vbTextCompare) <> 0 Then

etc

InStr will return the position that the text has been found or zero if it's not found.

Hope this helps

 

[Lenny Enfield]

Superb. Exactly what I needed. Thank you for explaining this function to me.