Refer Same Ranges Multiple Worksheets Using VBA

Accessing Same Ranges in Multiple Worksheets Using VBA
Working with 3D ranges

Working with 3-D Ranges using Excel macro
If you are working with the same range on more than one sheet, use the Array function in Microsoft Excel to specify two or more sheets to select.
The following macro example copies the Range A1:G1 data and fills it in all the worksheets of the Excel workbook. Next it selects all the sheets (1 to 6) using the Array method, selects the row 1, changes the font colour of the data of all the row ones in all the sheets to blue and finally activates the sheet1.

Sub FillAll()
Worksheets.FillAcrossSheets (Worksheets(“Sheet1”).Range(“A1:G1”))
Sheets(Array(“Sheet1”, “Sheet2”, “Sheet3”, “Sheet4”, “Sheet5”, “Sheet6”)).Select
Rows(“1:1”).Select
Selection.Font.ColorIndex = 5
Sheets(“Sheet1”).Activate
End Sub

Watch the Excel training video below to see the macro in action

Further reading:
Run Same VBA Macro Code On Multiple Sheets

Leave a Reply

Your email address will not be published. Required fields are marked *