Copy first row of Sheet1 to all worksheets in workbook with blank first row

[rdoulaghsingh]

Hello! I'm trying to write an Excel VBA script to copy the first row of "Sheet1" with current formatting and filter on and paste the values into the first row of every worksheet in the workbook if the worksheet is blank.

 

[JoeMo]

Maybe:

Sub CopyRow1If()
For Each sht In ActiveWorkbook.Worksheets
    If sht.Name <> "Sheet1" And Application.CountA(sht.Cells) = 0 Then
        With Sheets("Sheet1")
            .Rows(1).Copy
            sht.Rows(1).PasteSpecial xlValues
        End With
    End If
Next sht

End Sub

 

[rdoulaghsingh]

Maybe:

Sub CopyRow1If()
For Each sht In ActiveWorkbook.Worksheets
    If sht.Name <> "Sheet1" And Application.CountA(sht.Cells) = 0 Then
        With Sheets("Sheet1")
            .Rows(1).Copy
            sht.Rows(1).PasteSpecial xlValues
        End With
    End If
Next sht

End Sub

This is great! Thanks for the reply. Is there anyway to set the autofilter property on the first row of all sheets in this loop?

 

[JoeMo]

This is great! Thanks for the reply. Is there anyway to set the autofilter property on the first row of all sheets in this loop?

You are welcome - thanks for the reply.
Is this what you want for autofilter?

Sub CopyRow1If()
For Each sht In ActiveWorkbook.Worksheets
    If sht.Name <> "Sheet1" And Application.CountA(sht.Cells) = 0 Then
        With Sheets("Sheet1")
            .Rows(1).Copy
            sht.Rows(1).PasteSpecial xlValues
            Intersect(sht.UsedRange, sht.Rows(1)).AutoFilter
        End With
    End If
Next sht

End Sub