Attribute VB_Name = "V08_Clean_Code"
Option Explicit

' ------------------------------------------------------------------
' 1. What the Macro Recorder wrote. Task: copy the latest month of the
'    tracker to sheet Latest Month. Recorded when the last month was in
'    row 28. Every line works on the selection, and row 28 is fixed.
' ------------------------------------------------------------------
Sub Copy_Latest_Recorded()
'
' Copy_Latest_Recorded Macro
'
    Sheets("Tracker").Select
    Cells.Find(What:="Month", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=12
    Rows("28:28").Select
    Selection.Copy
    Sheets("Latest Month").Select
    Range("A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

' ------------------------------------------------------------------
' 2. The tempting shortcut: shorter, but it trusts the saved Find
'    settings and End(xlDown). If the subtitle contains the word
'    "month", it finds the subtitle, not the header.
' ------------------------------------------------------------------
Sub Copy_Latest_Halfway()
    Dim lastRow As Long
    lastRow = Cells.Find(What:="Month").End(xlDown).Row
    Sheets("Latest Month").Range("A2:E2").Value = Sheets("Tracker").Range("A" & lastRow & ":E" & lastRow).Value
End Sub

' ------------------------------------------------------------------
' 3. The clean version: no selection, no clipboard, no fixed rows.
'    Sheet names and the header text are read from sheet Settings.
' ------------------------------------------------------------------
Sub Copy_Latest_Month()
    Dim wsSet As Worksheet, wsSrc As Worksheet, wsOut As Worksheet
    Set wsSet = ThisWorkbook.Worksheets("Settings")
    Set wsSrc = ThisWorkbook.Worksheets(CStr(wsSet.Range("B3").Value))
    Set wsOut = ThisWorkbook.Worksheets(CStr(wsSet.Range("B4").Value))

    Dim headerText As String
    headerText = CStr(wsSet.Range("B5").Value)

    ' Find the header cell: every Find setting stated, result tested
    Dim headerCell As Range
    Set headerCell = wsSrc.Cells.Find(What:=headerText, LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False)
    If headerCell Is Nothing Then
        MsgBox "Header """ & headerText & """ not found on sheet " & wsSrc.Name & ".", vbExclamation
        Exit Sub
    End If

    ' Last row of the header's column, counted from the bottom of the sheet
    Dim lastRow As Long, nCols As Long
    lastRow = wsSrc.Cells(wsSrc.Rows.Count, headerCell.Column).End(xlUp).Row
    If lastRow = headerCell.Row Then
        MsgBox "No data below the header on sheet " & wsSrc.Name & ".", vbExclamation
        Exit Sub
    End If
    nCols = wsSrc.Cells(headerCell.Row, wsSrc.Columns.Count).End(xlToLeft).Column - headerCell.Column + 1

    ' Direct value assignment: header row and latest row, no clipboard
    wsOut.Range("A1:A2").EntireRow.ClearContents
    wsOut.Range("A1").Resize(1, nCols).Value = headerCell.Resize(1, nCols).Value
    wsOut.Range("A2").Resize(1, nCols).Value = wsSrc.Cells(lastRow, headerCell.Column).Resize(1, nCols).Value
    wsOut.Range("B4").Value = lastRow                     ' where the row came from, for checking
End Sub

' ------------------------------------------------------------------
' Helpers for the exercise
' ------------------------------------------------------------------
' Appends the row on sheet New Month below the tracker, once.
Sub Add_New_Month()
    Dim wsSrc As Worksheet, wsNew As Worksheet
    Set wsSrc = ThisWorkbook.Worksheets("Tracker")
    Set wsNew = ThisWorkbook.Worksheets("New Month")

    Dim lastRow As Long
    lastRow = wsSrc.Cells(wsSrc.Rows.Count, 1).End(xlUp).Row
    If wsSrc.Cells(lastRow, 1).Value = wsNew.Range("A2").Value Then Exit Sub   ' already added
    wsSrc.Range("A" & lastRow + 1).Resize(1, 5).Value = wsNew.Range("A2:E2").Value
End Sub

Sub Clear_Latest_Month()
    With ThisWorkbook.Worksheets("Latest Month")
        .Range("A1:A2").EntireRow.ClearContents
        .Range("B4").ClearContents
    End With
End Sub