Macro to Copy and Paste Values 3 rows before text "Total"

[howard]

I have text Total" in Col A

I have written code to copy & paste values in C2:D & Last row (LR) F2:G & Last row

I need the formula to be amended so that the last row is 3 rows before "Total" in Col A

Your assistance is most appreciated

 Sub Range_ValueFormulas()
Dim lr As Long
 With Sheets("Cash Sales")
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("C2:D" & lr).Copy
.Range("C2").PasteSpecial Paste:=xlPasteValues
.Range("F2:G" & lr).Copy
.Range("F2").PasteSpecial Paste:=xlPasteValues
.Application.CutCopyMode = False


  End With
  
End Sub

 

[JoeMo]

Untested.

Sub Range_ValueFormulas()
Dim Fnd As Range, lr As Long
 With Sheets("Cash Sales")
Set Fnd = .Range("A:A").Find("Total", LookIn:=xlValues, lookat:=xlWhole)
If Not Fnd Is Nothing Then
    lr = Fnd.Row - 3
Else
    MsgBox "Can't find ""Total"" in column A - exiting  sub"
    Exit Sub
End If
.Range("C2:D" & lr).Copy
.Range("C2").PasteSpecial Paste:=xlPasteValues
.Range("F2:G" & lr).Copy
.Range("F2").PasteSpecial Paste:=xlPasteValues
.Application.CutCopyMode = False
  End With
End Sub

 

[howard]

Hi Joe

Thanks for the help. Code works perfectly

 

[JoeMo]

Hi Joe

Thanks for the help. Code works perfectly

You are welcome - thanks for the reply.