When you're doing quality control processes where you compare values day-over-day in Excel, you'll often need to "roll" data on the worksheets so that you can build a new comparison. This macro assumes you have two sheets, one named "Current Day" and one named "Prior Day" and moves the information from the Current Day worksheet to the Prior Day …

vbaRollDays

Public Sub RollDays()
 
    Dim wksCd As Worksheet, wksPd As Worksheet
    Dim rngCd As Range, rngPd As Range
    Dim lngCdLc as Long, lngPdLc as Long, lngMaxLc as Long
    Dim lngCdLr As Long, lngPdLr As Long, lngMaxLr As Long
   
    With ThisWorkbook
        Set wksCd = .Worksheets("Current Day")
        Set wksPd = .Worksheets("Prior Day")
    End With
   
    lngCdLr = LastRow__lng(wksCd)
    lngPdLr = LastRow__lng(wksPd)
    lngCdLc = LastCol__lng(wksCd)
    lngPdLc = LastCol__lng(wksPd)
    lngMaxLr = WorksheetFunction.Max(lngCdLr, lngPdLr)
    lngMaxLc = WorksheetFunction.Max(lngCdLc, lngPdLc)
    With wksCd
        Set rngCd = .Range(.Cells(1, 1), .Cells(lngMaxLr, lngMaxLc))
    End With
 
    With wksPd
        Set rngPd = .Range(.Cells(1, 1), .Cells(lngMaxLr, lngMaxLc))
    End With
 
    rngPd.Value = rngCd.Value
 
End Sub