Loop through for WS names LIKE "AAA_####A" not working and throwing a runtime error 9

[juscuz419]

I have a worksheet that can contain up to 40 worksheets with a name LIKE "AAA_####A" where A can be any letter and # can be any number. I am trying to loop through each one in turn, extract data, paste it on another worksheet then go to the next worksheet. The code below is where I am, but it does not work. It throws an error code on the row in red font that the subscript is out of range. If I replace (sourcewsname) with an actual name of one of the sheets (ex MRC_4699A) the code works. Why wont the loop through work?

Sub CopyValuesBetweenWorksheets()

Dim sourceWS As Worksheet
Dim targetWS As Worksheet
Dim ws As Worksheet
Dim WSName As String
Dim wsDailySummary As Worksheet
Dim sourceWSName As String

For Each ws In ThisWorkbook.Worksheets
If ws.Name Like "???_?????" Then
sourceWSName = ws.Name
Exit For
End If

Set sourceWS = ThisWorkbook.Worksheets(sourceWSName)
Set targetWS = ThisWorkbook.Worksheets("Daily Summary")

'My code that works

Next ws
End Sub

 

[juscuz419]

I tend to focus on one thing at a time. You could probably tell that from the code I posted. I figure out a way to do one thing then I try to wrap something else around it and so on.
Completely misunderstood you second comment in Post #18. I banged on the new code trying to find a way to break it. It looks like you have solved this problem for me too. This works now and I added a way to email it as a PDF attachment to the workbook. One more enhancement and I think i will put this one to bed for awhile. REALLY appreciate you help and guidance.