VBA not selecting worksheet with titles

[Prets]

Hi,

I think its the late night but I would like some help with a code whic
I am struggling with.

To summarise my issue, I need this vba code to look at the worksheet
which contains the words "@testing.com", select a specific cell an
paste some words.

Now I have got the last bit working but the main issue is that vba doe
not select the worksheet which contains "@testing.com".
If the worksheets are called "testing1", "testing2", etc, I can get vb
to work but its when I tell it to look for a sheet name containin
"testing.com", it doesnt.

At the moment, the sheets are called (e-mail address removed), (e-mail address removed)
and (e-mail address removed)


Sub ConsolidateHoldings()

Dim mysheet As Worksheet

Sheets("master").Select
Cells.Clear

For Each mysheet In Worksheets

mysheet.Select

If Left(mysheet.Name, 6) Like "testing.com" Then

Range("f2").Select
ActiveCell.FormulaR1C1 = "Hi,"
Range("f4").Select
ActiveCell.FormulaR1C1 = "Please review the data on the left"
Range("f6").Select
ActiveCell.FormulaR1C1 = "If this is incorrect, please advise?"
Range("f8").Select
ActiveCell.FormulaR1C1 = "Thanks"

End If

Next mysheet
Range("A1").Select

End Sub


Hopefully this is an easy fix but I would like some help please.

Thanks
Pri

P.s. sorry, I tried to load the excel file but I couldnt

 

[GS]

Try...

Sub ConsolidateHoldings2()
Dim wks As Worksheet

Sheets("Master").Cells.Clear
For Each wks In ActiveWorkbook.Worksheets
If UCase$(Right(wks.Name, 11)) = "TESTING.COM" Then
With wks
.Range("F2") = "Hi"
.Range("F4") = "Please review the data on the left"
.Range("F6") = "If this is incorrect, please advise!"
.Range("F8") = "Thanks"
End With 'wks
End If
Next 'wks
End Sub 'ConsolidateHoldings2

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Prets]

Hi Garry,

Thats perfect. Even better than I had originally planned.

Thank you so much.

Pri

;1609120']Try...

Sub ConsolidateHoldings2()
Dim wks As Worksheet

Sheets("Master").Cells.Clear
For Each wks In ActiveWorkbook.Worksheets
If UCase$(Right(wks.Name, 11)) = "TESTING.COM" Then
With wks
.Range("F2") = "Hi"
.Range("F4") = "Please review the data on the left"
.Range("F6") = "If this is incorrect, please advise!"
.Range("F8") = "Thanks"
End With 'wks
End If
Next 'wks
End Sub 'ConsolidateHoldings2

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussio

 

[GS]

You're welcome! Always glad to help...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion