I want to check every cell on column G in sheet 2 for "Savings"
If "Savings" is found, then I want the next value pasted on sheet 5. Offset(1) doesn't work because the data below "Savings" may have a random number of blank rows.

This code works if I have Sheet 2 active before I run the macro. It doesn't work properly if I have any other sheet open though. I highlighted the issue in red. I have no idea why With isn't working properly. (Even if I do something simple like With s2, the Range/Cells commands don't seem to work properly for me)

    Dim s2 As Worksheet
    Dim s5 As Worksheet

    Set s2 = Worksheets("Sheet 2")
    Set s5 = Worksheets("Sheet 5")

   With s2.Range("G2", s2.Range("G" & s2.Rows.Count).End(xlUp))
        For Each j In s2.[G1:G1000]
            If j = "Savings" Then
                j.Select
                Selection.End(xlDown).Select
                ActiveCell.Copy
                
                With s5
                lst = .Range("E" & Rows.Count).End(xlUp).Row + 1
                .Range("E" & lst).PasteSpecial xlPasteValues
                End With
            End If
        Next j
    End With