Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Mirroing a coulmn from one Sheet to other sheets in the same file

0

Hello

I have an excle file only Sheet 1 is the master where I will add weekly data.

sheets 2-8 are teams sheet and I would like that ALL data in Sheet 1 coulmn A will be updated in sheet 2-8.

I know how to do it in single by using the formula =(Sheet#) but I would like the data to be updated in all sheets. can you help me with the Macro?

Answer
Discuss

Answers

0
sub copyColumn()
Sheets("sheet1").range("A1:A100").Copy Sheets("sheet2").range("A1")
End sub

Repeat this line of code for each sheet.

You can read more about copy past macros in our tutorial: copy paste macro in excel

Discuss
0

You might achieve this without VBA. Please try the following steps.

  1. 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.
  2. 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))
  3. 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.
  4. 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.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login