Macro apply 2nd Row format to entire row data in each worksheet of workbook

Vbalearner85

Board Regular
Joined
Jun 9, 2019
Messages
85
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I want to copy 2nd row format of each worksheet to entire data in same worksheet of the workbook

Please note:-

Data in each worksheet has similar format and same number of columns
Some columns have lesser data(rows) as compared to other columns
Columns data is continuous/no blanks
Column data ends at Column AE

Regards,
PK
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

does this work as expected?

VBA Code:
Sub copy_row_2_format()
    Application.ScreenUpdating = False
    For MY_SHEETS = 1 To ActiveWorkbook.Sheets.Count
        With Sheets(MY_SHEETS)
            .Rows(2).Copy
            .Rows("3:" & .Range("A" & Rows.Count).End(xlUp).Row).PasteSpecial (xlPasteFormats)
        End With
    Next MY_SHEETS
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,528
Messages
5,548,576
Members
410,852
Latest member
WernerS
Top