Search mutli sheets and capture

Hi There All Gurus

I need Help - I have created a mess and had to start from scratch and need your help.

I have to assemble a Asset Register per Department with the last service date and the Next service date. I want to create a VBA to search all the sheets (Tabs) and retrieve all the equipment which has to be serviced on the month I select in the "Monthly service" Tab.

Please see the attachment.

I hope someone can help me

Much appreciated

Try this

PHP Code: 

Sub summary()
    Dim MonthRng As Range
    Dim i As Integer
    Dim sh As Worksheet
    Dim M As String
    Dim shMonthly As Worksheet
    
    Set shMonthly = Sheets(1)
    Set MonthRng = Sheet2.Range("A2:A13").Find(CStr(shMonthly.Range("C1").Value), , xlValues, xlWhole)
    For Each sh In ThisWorkbook.Worksheets
        
        If sh.Name <> "Monthly Service" And sh.Name <> "CONDITIONS" Then
            For i = 3 To sh.Range("A" & Rows.Count).End(xlUp).Row
                M = Val(MonthRng.Offset(, 1).Value)
                If Month(sh.Range("H" & i).Value) = M Then
                    With shMonthly
                        .Range("B" & .Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row).Value = sh.Range("I" & i).Value
                        .Range("C" & .Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row).Value = sh.Range("B1").Value
                        .Range("D" & .Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Row).Value = sh.Range("C" & i).Value
                        .Range("E" & .Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Row).Value = sh.Range("A" & i).Value
                    End With
                End If
            Next i
            End If
    Next sh
End Sub 


In the worksheet "Monthly Service", change C1 to a number, ie. Jan would be 1, Feb would be 2, etc. Then apply this code

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

Originally Posted by ronettes

Hi ImranBhatti

Should I paste this in a Button, or just on the page under General?

Thank you so much

You have created a button "BUTTON: UPDATE SERVICE???" . The code linked to that button.