CODEWITHSUNDEEP
excelvbamirrorworksheet
user1628421
user1628421

Reputation: 11

Mirroring column in one excel sheet to other multiple excel sheets with automatic updating

I have an excel workbook that contains multiple sheets within it. For the sake of this question, the sheets are named Sheet1, Sheet2, Sheet3, and so on. I would like to have Column A from sheet1 be replicated throughout the rest of the sheets and as new cells are added to column A in sheet1, they would automatically be entered into the other sheets within the workbook. I would prefer not to have a set "ending range; ie: A100000" for this. For example, if I enter First in cell A1 of Sheet1, the word "First" should now also appear in cell A1 of Sheet2. I have used the following code, and it does not seem to work. Any help would be greatly appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
        Call UpdateFromSheet1
End Sub

Sub UpdateFromSheet1(ByVal Sh As Object, ByVal Target As Range)
    If Sh.CodeName = "Sheet1" Then
        If Not Intersect(Target(1, 1), Range("A1:A1000")) Is Nothing Then
            Sh.Range("A1:A1000").Copy Sheet2.Range("A1")
        End If
    End If
End Sub

Upvotes: 1

Views: 14067

Answers (2)

Scott Holtzman
Scott Holtzman

Reputation: 27249

UPDATE

For a clean looking Non-VBA solution, you can use the formula references that others have mentioned, but enter it like this.

In Sheet2 cell A1 = If(Sheet1!A1="","",Sheet1!A1) That way you can fill down on the whole of column A and not have "0" pop-up if Sheet1 has a rows without data.

I think you have the general idea, but I suspect you may not have your code in the right place.

For the VBA solution:

First, you don't need to call the sub from Worksheet_Change event (unless of course you want to use this sub for other reasons and pass variables to it. Second, if you place this code in the worksheet object in the VBE of the "Sheet1" it will do as you wish:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Columns("A")) Is Nothing Then

    Dim wks As Worksheet
    For Each wks In Sheets(Array("Sheet2", "Sheet3"))

        Target.EntireColumn.Copy wks.Columns(1)

    Next

End If


End Sub

Upvotes: 1

Stepan1010
Stepan1010

Reputation: 3136

This is a very basic use for excel. You can set cells equal to each other. You in no way would need a VBA macro for this.

If you put this in cell "A1" on Sheet2 and Sheet3:

=Sheet1!A1

Then when you type something into A1, it will be "mirrored" on sheets 2 and 3. You can autofill this down to all the cells in column A on sheets 2 and 3.

Are you familiar with the term autofill?

If you don't understand anything I just said and you want to just run a macro then run this and start typing away:

Sub MacroBasicQuestion()

Dim wbk As Workbook
Set wbk = ThisWorkbook

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Set ws1 = wbk.Sheets(1)
Set ws2 = wbk.Sheets(2)
Set ws3 = wbk.Sheets(3)

Dim cell As Range

Set cell = ws2.Range("A1:A1")
ws2.Select
cell.FormulaR1C1 = "=Sheet1!RC"
cell.Select
Selection.AutoFill Destination:=Range("A:A"), Type:=xlFillDefault

Set cell = ws3.Range("A1:A1")
ws3.Select
cell.FormulaR1C1 = "=Sheet1!RC"
cell.Select
Selection.AutoFill Destination:=Range("A:A"), Type:=xlFillDefault

End Sub

Good Luck.

Upvotes: 0

Related Questions