Selected Answer
You might achieve this without VBA. Please try the following steps.
- On your master sheet create a named range. In this example I call it "Master". The quickest way to do that is to select the range and type the name in the Name Box (left of the Formula Bar above the ruler, usually showing the active cell's address).
The range should comprise all the rows you want to copy to the other sheets. For ease of handling you can make it big enough to accommodate the maximum number of rows you might have, even if some remain blank at times.
Modify the range using the Name Manager on the Formulas tab.
- On one of the other sheets, select an area in column A which is of equal size to the "Master" range you created on your master sheet. Then type the formula below in the formula bar (you can also type it into the first cell of the selected range).
=IF(ISNA(INDEX(Master,ROW(Master)-1)),"",INDEX(Master,ROW(Master)-1))
- This is an array formula. Therefore it must be confirmed with Ctl+Shift+Enter instead of the singular Enter you are accustomed to when entering normal formulas. Meaning, when you finish typing the formula, hold down Ctl and Shift and press Enter before you release the other two keys.
If entered correctly, the formula will appear automatically in all the selected cells. Excel will also surround it with curly braces.In order to modify it in future you will have to select all the cells, then make the modification in the first one.
- Now copy the column A with the formula to all the other sheets and you are set to go.
Observe the "-1" appearing twice in the formula. It isn't needed. (Haha - very funny). The formula copies the cell contents of the first row in "Master" to the row number of the first cell in the array formula's range. In my example that was row 2. Therefore I wanted row 1 of "Master" in Row(2) of the target sheet. Hence "Row(2)-1" returning the row number of "Master". If "Master" would start from A1 there would be no difference and the formula could be adjusted to "-0" or omit the number completely. On the other hand, if "Master" row 1 should be in A3 in the sub-sheets, the difference would grow to 2 and the formula should be adjusted accordingly.
If the formula range on the sub-sheets is bigger than the "Master" range an error will occur which the formula suppresses.