Macro to compare previous worksheet only to new worksheet.

[Levi2020]

I have a workbook that is used to keep track of hub mileage (in column B) and hours ran (in column C) of trailers. A new sheet is added weekly to record that weeks data.
I am trying to come up with a macro that will compare only the previous weeks data and throw up an alert if the mileage gap is greater than
2500 and hours gap is greater than 150. Can anyone help with this?

 

[onlyadrafter]

Hello.

Care to post some sample data, with description of tab names and how new ones are created, and how you want the alert to work.

 

[Levi2020]

Tab names are just the date that the current week is completed. It's just a basic sheet. New tabs were being created manually until I added this macro.
Sub CopyRename()
Dim sName As String
Dim wks As Worksheet
Worksheets("MASTER3").Copy after:=Sheets(Worksheets.Count)
Set wks = ActiveSheet
Do While sName <> wks.Name
sName = Application.InputBox _
(Prompt:="Enter new worksheet name")
On Error Resume Next
wks.Name = sName
On Error GoTo 0
Loop
Set wks = Nothing
End Sub

A

UNIT	HUB MILES	2500	HOURS	150
S5316280	86994	____	10247	____
S5316281	74496	____	11746	____
S5316282	83207	____	10461	____
S5316283	76009	____	9647	____
S5316284	77772	____	10689	____
S5316285	90824	____	11287	____
S5316286	77509	____	10058	____
S5316287	79138	____	10836	____

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Right now this formula is being used =IF('8.21.2019'!B2+'8.28.2019'!$C$1<='8.28.2019'!B2,"RECHECK","____"). This however has to be changed on each new sheet. What I would like is be able to run a macro after all new data is entered to do this check automatically and maybe highlight the cells of those that need rechecking. The issue I am having is I can't find anything that references comparing the previous page to current page only. These pages will change weekly as you add new.

 

[onlyadrafter]

Hello,

Have included your code and added the bottom few lines.

Sub CopyRename()
Dim sName As String
Dim wks As Worksheet
Worksheets("MASTER3").Copy after:=Sheets(Worksheets.Count)
Set wks = ActiveSheet
Do While sName <> wks.Name
sName = Application.InputBox _
(Prompt:="Enter new worksheet name")
On Error Resume Next
wks.Name = sName
On Error GoTo 0
Loop
Set wks = Nothing
'code to insert formula
    MY_LAST = Sheets(Worksheets.Count - 1).Name
    MY_NEW = Sheets(Worksheets.Count).Name
    Range("C2").Formula = "=IF('" & MY_LAST & "'!B2+'" & MY_NEW & "'!$C$1<='" & MY_NEW & "'!B2," & """RECHECK""" & "," & """-----""" & ")"
    Range("C2").Copy Range("C3:C" & Range("B" & Rows.Count).End(xlUp).Row)
End Sub

 

[Levi2020]

I appreciate your help. This has gotten me farther than I could have gotten myself. The only issue I am having now is the formula is pasting to the master sheet instead of the newly created sheet. Any ideas?

Sub CopyRename()

Dim sName As String
Dim wks As Worksheet

Worksheets("MASTER3").Copy after:=Sheets(Worksheets.Count)
Set wks = ActiveSheet
Do While sName <> wks.Name
sName = Application.InputBox _
(Prompt:="Enter new worksheet name")

On Error Resume Next
wks.Name = sName

On Error GoTo 0
Loop
Set wks = Nothing


MY_LAST = Sheets(Worksheets.Count - 1).Name
MY_NEW = Sheets(Worksheets.Count).Name
Range("C2:C56,C58:C78").Formula = "=IF('" & MY_LAST & "'!B2+'" & MY_NEW & "'!$C$1<='" & MY_NEW & "'!B2," & """RECHECK""" & "," & """-----""" & ")"
Range("E2:E56,E58:E78").Formula = "=IF('" & MY_LAST & "'!D2+'" & MY_NEW & "'!$E$1<='" & MY_NEW & "'!D2," & """RECHECK""" & "," & """-----""" & ")"
Range("C2:C56,C58:C78").Copy Range("C3:C" & Range("B" & Rows.Count).End(xlUp).Row)
Range("E2:E56,E58:E78").Copy Range("E3:E" & Range("D" & Rows.Count).End(xlUp).Row)






End Sub

 

[Levi2020]

Never mind, I got it figured out. Thank you for your help. You are the best!