Work for more than one sheet in workbook

[MrNew]

Hi

I am very new to what excel can do and am learning as I go so sorry if this is a daft question or the code is completely wrong.
I have put together the following code, using posts on this site, which works for the one worksheet ("one") however I can not get it to work across all the work sheets, (there are 16 in total). I have tried various ways found on the net but nothing seems to work, I think due to my lack of knowledge. Each worksheet is a number ie one, two, three etc.
Any help word be appreciated.

Private Sub Workbook_Open()

Dim ThisCell1 As Range
Dim ThisCell2 As Range
For Each ThisCell1 In Worksheets("one").Range("M10:M20")
For Each ThisCell2 In Worksheets("one").Range("M6")
If ThisCell1.Value = ThisCell2.Value Then
Worksheets("one").Range("M1").Value = month(Date) - 1

Exit For
End If
Next ThisCell2
Next ThisCell1

End Sub

 

[My Aswer Is This]

Please explain what your script should do. it appears the only thing the script does is put the previous month's number in cells "M1"

 

[MrNew]

Yes, basically it does, it checks cells M10:M20 looking for last months date and then displays the months number in "M1".
What I am trying to do is check the columns on each sheet and when any entry matches last months date display a name and month on another workbook. This all works ok for this one sheet but no more.
I am currently referencing the date in "M1" on another workbook and if it is present displaying it and an associated name.
I am sure this is not the best way to do this but its what I have managed to get to work so far with my limited knowledge.

Sorry I should have tried explaining what I was trying to do in the first place. Does this help?

 

[My Aswer Is This]

Your quote: I am currently referencing the date in "M1" on another workbook and if it is present displaying it and an associated name.

I do not see anything in your script that deals with another Workbook
And I do not understand " and an associated name."
Be sure and use correct terms "Worksheet" "Workbook"

 

[dmt32]

Hi,
based on code posted, this should do what you want for each of your 16 worksheets in your workbook

Private Sub Workbook_Open()
    Dim m As Variant
    Dim rng As Range
    Dim i As Integer


    For i = 1 To 16
        With Worksheets(i)
            Set rng = .Range("M10:M20")
            m = Application.Match(CLng(.Range("M6")), rng, False)
            If Not IsError(m) Then .Range("M1").Value = Month(Date) - 1
    End With
            Set rng = Nothing
    Next i
End Sub

Note - I have used the sheets index property which requires that they are the FIRST 16 sheets in your workbook with NO chart sheets between them.
I also assume that values you are checking are DATES.

Hope helpful

Dave.

 

[MrNew]

Thanks dmt32 that works a treat

 

[dmt32]

Thanks dmt32 that works a treat

welcome - thanks for feedback

Dave