I tried the With Sheets part, but maybe I have it in the wrong
place...

Sub CopyLastSheetAndName()
Dim ls As Long
ls = ActiveWorkbook.Sheets.Count
'MsgBox ls
Sheets(Sheets.Count).Copy after:=Sheets(ls)
ActiveSheet.Name = "Rpt #" & ls + 1
Range("i4").Select
ActiveCell.FormulaR1C1 = Now()
With Sheets(ls).Range("d9")
.Value = Value + 1
End With
Range("D10:G10").Select
ActiveWindow.ScrollColumn = 2

Range("T15:T56").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-15
Range("S15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 5

Range("A14:J21").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveWindow.SmallScroll Down:=42
Range("P75:P79").Select
Selection.Copy
Range("L75:L79").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("M75:O79").Select
Application.CutCopyMode = False
Selection.ClearContents
End Sub