Microsoft Office Forums - View Single Post

Philb1 · 03-05-2016, 01:54 AM

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

Code:

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