How to modify a macro to autofill a range with specific cell content?

Question

I'm trying to automate a report into a layout the boss wants it in, I have it taken out the sales program and am adding a 1-30, 31-60, 61+ date range for sales, I have added a formula to column S,T,U with this '=IF(R4="","",IF($A4=1,IF(AND($P4<=30),$R4,0),"autosum")) changing the $P4<= value for the different number of days

I then select S:U copy and paste as values to remove the formulas, but what I am having an issue with is I need to autofill the contents from R to S:U that has autosum as the cell content

I am hoping for a macro recommendation as the report has thousands of lines, I am trying to search for autosum, use activecell.offset(0,-1) then autofill. But I am having an issue with the range to fill.

This is the current macro that stops working after application.cutcopymode=false with an application-defined error message.

Sub autosum()
'
' autosum Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
    Range("A1").Select
    Cells.Find(What:="autosum", After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, -1).Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("R7:U7"), Type:=xlFillDefault
    Range("R7:U7").Select
End Sub

any help would be much appreciated.

I have tried with activecell.range(r:u), but after this point I am out of my depth with modifying macros.

Answer 1

' ALL THE CODE IN THE SHEET'S MODULE
Option Explicit

Private Sub BT_FIND_AND_COPY_Click()
   Call fillAutosums
End Sub

' We have to scan ONLY column S and when find a cell with "autosum"
' get the cell on the left => R, and autofill included R four cells on the right => R,S,T,U
Private Sub fillAutosums()
   Dim r As Range, frow As Long, lastFrow As Long, curRow As Long
   Dim firstCell As String, lastCell As String, lastRow As Long
   
   ' find the last used row of the sheet
   lastRow = Me.UsedRange.rows.CountLarge
   firstCell = "S1"
   lastCell = "S" & lastRow
   ' frow and lastFrow are initially equal to 0
   On Error GoTo Lexit
   Do
      frow = WorksheetFunction.Match("autosum", Me.Range(firstCell & ":" & lastCell), 0)
      ' If we are here something from Match will have been found, otherwise
      ' there would have been fired an error
      ' we found a cell with "autosum"
      ' the row is relative to the range use in Match. To calculate the
      ' row in sheet add the last found row
      curRow = frow + lastFrow
      ' get in r the cell on the left R...
      Set r = Me.Range("R" & curRow)
      ' autofill form r to => resizing r to include S,T,U
      r.AutoFill Destination:=r.Resize(1, 4), Type:=xlFillDefault
      ' if the found row is the last of used range => no more work to do
      If frow >= lastRow Then Exit Do
      ' the next Match to begin from the next row => row after curRow
      firstCell = "S" & (curRow + 1)
      ' make the last found row equal to this found row
      lastFrow = curRow
   Loop
Lexit:
   On Error GoTo 0
End Sub

Showing Formulas Before and after AutoFill