Refresh Spreadsheet data and Pop-up message based on critera

[DarinM]

Hi again forum!

I have a bunch of different tracking spreadsheets that I want to combine into one spreadsheet and have different tabs. Sounds like a good idea...

However, I would like it to be a tiny bit more automated/up to date WITHOUT closing the excel document.

I have a bunch of IF statements and conditional formatting, some are based on =TODAY() 's formula for dates. However I have noticed that I need to close and re-open the excel for the =TODAY() to refresh to today's true date, then the IF statements etc are up to date and correct.

So my hopeful solution for this would be two parts.

Part 1; Have a "REFRESH" button on each tab that would refresh a certain column that would have all the =TODAY() dates in them, so that they are truly TODAY's date. I would want each button on each tab to refresh all tabs (so whichever I leave open I can click on one, knowing it will refresh all).

Part 2; After clicking that "REFRESH" button, I would like a macro to search through all tabs and let me know when I need to do something, based on the existing IF & conditional formatting forumals.

For example : I have a formula that compares =TODAY() column with a "ship on this date" column, if the two match, it makes another column say "SHIP TODAY". So I would like that refresh button to go through and refresh the TODAY's dates, then if the dates match, have a Pop-up with what my one column says "SHIP TODAY".

Is this possible?

I can attach a spreadsheet if required to see more what I mean, I have some code in 1 tracker, none in another, so I'm hoping this is a one size fits all type code.

Thanks all,

[jonh]

Sub test()
    Range("a1") = Range("a1")
    For Each sht In Worksheets
        Set x = sht.Cells.Find(what:="ship today", LookIn:=xlValues, MatchCase:=False, SearchFormat:=False)
        If Not x Is Nothing Then s = s & "Stuff to do! " & x.Parent.Name & " - " & x.Address & vbNewLine
    Next
    If Len(s) Then MsgBox s Else MsgBox "Put yer feet up."
End Sub

[DarinM]

Update, just found out F9 refreshes the date, which is easy... just wondering if that refreshes the current sheet or entire workbook? If entire workbook, then I guess I just need the popup based on 1 cell/columns value.

edit; Just saw your post JonH. I will try it, if else ...I'll put my feet up!

thank you, i'll check back shortly

[DarinM]

Hi JonH - That works amazingly, is there an easy way to add more than 1 "what:"ship today"'s ?

I will have about 5 or 6 things to search up and return

[jonh]

sure, here you go..

Sub test()
    MsgBox findthemall("ship today", "ship tomorrow", "ship to china")
End Sub

Private Function findthemall(ParamArray words() As Variant) As String
    Range("a1").Formula = Range("a1").Formula
    For Each w In words
        For Each sht In Worksheets
            Set x = sht.Cells.Find(what:=w, LookIn:=xlValues, MatchCase:=False, SearchFormat:=False)
            If Not x Is Nothing Then findthemall = findthemall & "Found @ '" & w & _
                "' in " & x.Parent.Name & " - " & x.Address & vbNewLine
        Next
    Next
    If Len(findthemall) = 0 Then findthemall = "No matches"
End Function

[DarinM]

Beautiful, thank you!

Do you know if F9 refreshes the entire workbook or just the active sheet? if just the active sheet, is there a button/macro I can have to update/refresh all sheets?

[jonh]

Np.

No idea about f9. Some info here.
https://msdn.microsoft.com/en-us/lib.../bb687891.aspx

I added the formula=formula line to the code because I thought that would suffice.

[DarinM]

Looks like F9 does entire workbook, I will do some testing to see ... however will take until tomorrow to find out

I appreciate your coding help! I think I have all I need, now to merge all trackers into 1.

Question though, does it matter the column that the "ship to china" is in? or just searches the entire workbook regardless of location? hoping regardless ... each tracker has a different spot for the signal words...

thanks again!

[jonh]

It searches the entire workbook, but that can easily be changed.

[DarinM]

Nope that works perfectly.

Thank you!!