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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

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,337
Messages
5,547,349
Members
410,785
Latest member
phillippaige
Top