Microsoft Excel Training

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







Reference

Working with 3D ranges using Excel macro