Excel question - auto copy info onto a second sheet

New Contributor

I want to create a spreadsheet where information from some selected cells is duplicated on a second sheet simultaneously.  Is there a way to do this?

For example, I want to enter property addresses in column A Sheet 1 and have the same information populate in column A Sheet 2 simultaneously.  Is this enough explanation to get some answers?  Sorry.  New to this.


5 Replies
Yes. But your description is not very complete. If you want a more complete answer, you'll have to say quite a bit more of the context in which you want to do that duplication of selected cells. And why.
Added a bit more info. Thanks.


Assuming you have two sheets called Sheet1 and Sheet2 in the file and you want to duplicate what you input in column A on Sheet1 on to the Sheet2, then follow the steps given below...


  1. Right Click on Sheet1 Tab Name and choose View Code.
  2. Copy the code given below and paste into the opened Code Window.
  3. Save your workbook as Macro-Enabled Workbook and you are good to go.

To test the code, now enter some values in column A on Sheet1 and those values will be copied to the Sheet2 automatically.



Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsDest  As Worksheet
Dim Cel     As Range

Set wsDest = ThisWorkbook.Worksheets("Sheet2")  'The changes on Sheet1 will be copied to Sheet2, Change the name of the destination sheet if required

If Not Intersect(Target, Range("A:A")) Is Nothing Then
    For Each Cel In Target
        wsDest.Range(Cel.Address).Value = Cel.Value
    Next Cel
End If

End Sub

And if you don't want a macro based solution, you may also place the following formula on Sheet2


On Sheet2

In A1


and copy it down the rows.

Did you notice I asked for not only a more complete description of the context for your request, but also the "Why"? What's the purpose for what you're trying to do?

You're clearly a beginner at some level (I say that because the question is quite basic), and for that reason it's also possible that you're designing something that is, in fact, not the best use of Excel. Excel is marvelously powerful, and marvelously powerful tools can be used wisely or not so wisely.

So if you don't mind--and I trust you'll forgive my impertinence in asking this--give a much more complete picture of what your end goal here is, why you think that having sheet two copy some (or all?) of what's one sheet one is the way to accomplish it. Redundancy sometimes makes sense. But often it doesn't. There are ways to extract data from sheet 1 onto sheet 2--myriad ways; the mostappropriate way will depend on your ultimate purpose.