Help With Copy Formula Down Macro

[npalmer610]

Hello. I was able to tweak a macro that was provided on another forum to copy a formula down to the last row (based on the adjacent column). I would like to instruct the code to run on all sheets in the workbook, so long as it's not a blank sheet, without having to click through each sheet then rerun the macro. Could someone please assist me with that? Thank you!

Sub CopytoLast()
Dim LastRow As Long
Columns("F").ClearContents
LastRow = Cells(Rows.Count, "E").End(xlUp).Row
Range("F2:F" & LastRow).Formula = "=CONCATENATE(A2,"" "", D2, "" "", E2)"
End Sub

[cobbe]

Sub CopytoLast()
Dim LastRow As Long
 Columns("F").ClearContents
  LastRow = Cells(Rows.Count, "E").End(xlUp).Row
   Range("F2").Formula = "=CONCATENATE(A2,"" "", D2, "" "", E2)"
    Range("F2").AutoFill Destination:=Range("F2:F" & LastRow)
End Sub

K.r., Cobbe

[Philb1]

This will loop through the sheets and provided the worksheet isn't blank, will run your code
Cheers

Option Explicit

Sub CopytoLast()
Dim LastRow As Long, shtCount As Long, i As Long

shtCount = ThisWorkbook.Worksheets.Count

For i = 1 To shtCount
    If Not Application.WorksheetFunction.CountA(ThisWorkbook.Sheets(i).Cells) = 0 Then
        Columns("F").ClearContents
        LastRow = Cells(Rows.Count, "E").End(xlUp).Row
        Range("F2:F" & LastRow).Formula = "=CONCATENATE(A2,"" "", D2, "" "", E2)"
    End If
Next i

Exit Sub
End Sub

[npalmer610]

Originally Posted by Philb1

This will loop through the sheets and provided the worksheet isn't blank, will run your code
Cheers

Option Explicit

Sub CopytoLast()
Dim LastRow As Long, shtCount As Long, i As Long

shtCount = ThisWorkbook.Worksheets.Count

For i = 1 To shtCount
    If Not Application.WorksheetFunction.CountA(ThisWorkbook.Sheets(i).Cells) = 0 Then
        Columns("F").ClearContents
        LastRow = Cells(Rows.Count, "E").End(xlUp).Row
        Range("F2:F" & LastRow).Formula = "=CONCATENATE(A2,"" "", D2, "" "", E2)"
    End If
Next i

Exit Sub
End Sub

Worked perfectly ... thank you!

[Philb1]

That's good