Running a "Last Seen Report" using the data in 1 Tab and viewing it in another Tab

[Wilkesy13]

Hi there,

I fear this may be a hugely technical request but have no doubt in the ability of the amazing people on here.

Question:
I have a spreadsheet that I use for monitoring assets, the assets are kept in one location but out of the pool of assets there are always some that are being used and so can be weeks or months until they are checked. I have the assets listed in a Tab in column A (for discussion purposes let's say 1-50). I have then grouped 5 columns (C-G, H-L, M-Q etc.) under specific dates/days of the week when certain checks are completed and headed as follows "Checked," "Checked By," "Ticket Ref:," "Condition" and "Fault." You can see how it works in the example below and all of the dates that the checks are performed are listed in Row 2.

The "Checked By," "Condition" & "Fault" cells all have drop down lists. When an option is chosen from the "Checked By" column it turns the "Checked" column green and places a 1 in the cell.

What I'd really like to be able to do, in another Tab, is be able to see when an asset was last checked. I have absolutely no idea if this is possible as I have only BASIC excel knowledge but if anyone has an idea or solution I will be very pleased to hear it.

I hope I have explained adequately but in anyone's quest for help, if you have questions please let me know.

For anyone that tackles this mighty challenge I thank you sincerely in advance.

 

[Joe4]

OK, it is very hard to program to someone that is a constant moving target! It seems that you have changed the layout of your form, as you added "Week End Totals" and "Year End Totals".
And your sample sheet that you posted has no actual data in it.

These totals totally mess up how I had it working, as now you might have "1"s in these total columns.
That renders pretty much all of my code obsolete.

 

[Wilkesy13]

OK, it is very hard to program to someone that is a constant moving target! It seems that you have changed the layout of your form, as you added "Week End Totals" and "Year End Totals".
And your sample sheet that you posted has no actual data in it.

These totals totally mess up how I had it working, as now you might have "1"s in these total columns.
That renders pretty much all of my code obsolete.

Hi Joe,

The only changes I made with the spreadsheets I shared the links to were to Sheet2 with adding the SN numbers to column B. All of the other data I shared was there from the get go.

Apologies if anything I did made it difficult. Had I known the additional data would have had an affect I would have mentioned it.

Sorry for the work you've done that has taken lots of your time and resulted in highlighting my poor communication skills.

You're knowledge and experience is eye opening and so I thank you

 

[Joe4]

I built the initial code off of your screen prints in your origina post, and post 9, and there are no "Weekly" or "Yearly" totals shown on there (presumably, because the image does not go out far enough to pick up the first one).

I made some updates, and ran it against your last data file, and it is returning results. Here is the code:

Sub MyPopulateData()

    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim lu As String
    Dim lr1 As Long
    Dim r1 As Long
    Dim lr2 As Long
    Dim r2 As Long
    Dim lc1 As Long
    Dim c1 As Long
    
    Application.ScreenUpdating = False
    
'   Set name of sheet where data resides
    Set sh1 = Sheets("Sheet1")
    
'   Set name of sheet where writing results
    Set sh2 = Sheets("Sheet2")
    
'   Find last row with data on data sheet
    lr1 = sh1.Cells(Rows.Count, "A").End(xlUp).Row
    
'   Find last row with data in column A on data sheet
    lr2 = sh2.Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all cells in column A on data sheet
    For r2 = 4 To lr2
'       Get value to lookup
        lu = sh2.Cells(r2, "B")
'       Find lookup value on data sheet
        For r1 = 4 To lr1
            If sh1.Cells(r1, "B") = lu Then
'               Find last column with data on data sheet (exclude last two columns)
                lc1 = sh1.Cells(r1, Columns.Count).End(xlToLeft).Column - 2
'               Loop through all columns backwards
                For c1 = lc1 To 3 Step -1
'                   Find last value equal to "1"
                    If sh1.Cells(r1, c1) = "1" And Right(sh1.Cells(3, c1), 6) <> "Totals" Then
'                       Populate results sheet with date
                        sh2.Cells(r2, "C") = sh1.Cells(2, c1)
                        Exit For
                    End If
                Next c1
            End If
        Next r1
    Next r2

    Application.ScreenUpdating = True

    MsgBox "Macro Complete!"

End Sub

 

[Wilkesy13]

I built the initial code off of your screen prints in your origina post, and post 9, and there are no "Weekly" or "Yearly" totals shown on there (presumably, because the image does not go out far enough to pick up the first one).

I made some updates, and ran it against your last data file, and it is returning results. Here is the code:

Sub MyPopulateData()

    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim lu As String
    Dim lr1 As Long
    Dim r1 As Long
    Dim lr2 As Long
    Dim r2 As Long
    Dim lc1 As Long
    Dim c1 As Long
   
    Application.ScreenUpdating = False
   
'   Set name of sheet where data resides
    Set sh1 = Sheets("Sheet1")
   
'   Set name of sheet where writing results
    Set sh2 = Sheets("Sheet2")
   
'   Find last row with data on data sheet
    lr1 = sh1.Cells(Rows.Count, "A").End(xlUp).Row
   
'   Find last row with data in column A on data sheet
    lr2 = sh2.Cells(Rows.Count, "A").End(xlUp).Row
   
'   Loop through all cells in column A on data sheet
    For r2 = 4 To lr2
'       Get value to lookup
        lu = sh2.Cells(r2, "B")
'       Find lookup value on data sheet
        For r1 = 4 To lr1
            If sh1.Cells(r1, "B") = lu Then
'               Find last column with data on data sheet (exclude last two columns)
                lc1 = sh1.Cells(r1, Columns.Count).End(xlToLeft).Column - 2
'               Loop through all columns backwards
                For c1 = lc1 To 3 Step -1
'                   Find last value equal to "1"
                    If sh1.Cells(r1, c1) = "1" And Right(sh1.Cells(3, c1), 6) <> "Totals" Then
'                       Populate results sheet with date
                        sh2.Cells(r2, "C") = sh1.Cells(2, c1)
                        Exit For
                    End If
                Next c1
            End If
        Next r1
    Next r2

    Application.ScreenUpdating = True

    MsgBox "Macro Complete!"

End Sub

This is incredible it works a treat! Thank you for not giving up on me! You are absolutely right and I apologise again, the initial screenshots didn't contain all of the information and I have learnt that lesson for future scenarios.

Last question, purely as I'm naïve to VBA, do I need to manually run the code every time I want the data populated or is there a way for it to run when the spreadsheet is opened or similar? Not that this is essential as it's working perfectly when I run the code. I just wasn't sure if there was something that I was or wasn't doing.

Thanks again Joe, this really has made a HUGE difference to what I'm trying to achieve

 

[Joe4]

You are welcome.

You can call this macro to run upon opening the workbook (provided macros/VBA re enabled) by calling this macro from the Workbook_Open Event Procedure (Event Procedures are events that trigger code to run automatically). So you would have code like this:

Private Sub Workbook_Open()
    MyPopulateData
End Sub

and it HAS to be placed in the "ThisWorkbook" module in order to run automatically.

 

[Wilkesy13]

You are welcome.

You can call this macro to run upon opening the workbook (provided macros/VBA re enabled) by calling this macro from the Workbook_Open Event Procedure (Event Procedures are events that trigger code to run automatically). So you would have code like this:

Private Sub Workbook_Open()
    MyPopulateData
End Sub

and it HAS to be placed in the "ThisWorkbook" module in order to run automatically.

So all I could find to do is have it placed at the bottom of the original code you got to work (as per the screen shot) but this doesn't seem right.

Also so I don't screw it up. Would the code still work if I were to add more rows to the bottom as long as I follow EXACTLY the same format as is already there?

 

[Joe4]

So all I could find to do is have it placed at the bottom of the original code you got to work (as per the screen shot) but this doesn't seem right.

No, you put it in the wrong place. You put it in "Module2".
Notice what I said at the end of my last response.

and it HAS to be placed in the "ThisWorkbook" module in order to run automatically.

This is non-negotiable. Event Procedure code only runs automatically when placed in the pre-existing "ThisWorkbook" or "Sheet" modules.
Since it is code that runs upon a workbook action, it must go in the "ThisWorkbook" module.
It also has to be named this way (cannot change the name of "Workbook_Open").

Adding rows or columns should not be an issue as long as you follow the same patterns.

 

[Wilkesy13]

No, you put it in the wrong place. You put it in "Module2".
Notice what I said at the end of my last response.


This is non-negotiable. Event Procedure code only runs automatically when placed in the pre-existing "ThisWorkbook" or "Sheet" modules.
Since it is code that runs upon a workbook action, it must go in the "ThisWorkbook" module.
It also has to be named this way (cannot change the name of "Workbook_Open").

Adding rows or columns should not be an issue as long as you follow the same patterns.

I appreciate what you're saying but no matter what I cannot find a "ThisWorkbook" Module anywhere. That's why I struggled the first time round. There is nothing that eludes to this or that I can easily find?

 

[Joe4]

If you do not see it, you need to open your Project Explorer in VBA.
You should then see your workbook name, and if you expand it out under that, you will see this.
Here is a guide that shows you how to do all of that: MS Excel 2013: Project Explorer in VBA Environment

 

[Wilkesy13]

If you do not see it, you need to open your Project Explorer in VBA.
You should then see your workbook name, and if you expand it out under that, you will see this.
Here is a guide that shows you how to do all of that: MS Excel 2013: Project Explorer in VBA Environment

Done, Done and Done!

Completed and working exactly how I had hoped. You're AMAZING
Thank you and sorry for holding it all back with my terrible communication