Copy Source Cells to Destination Cells Only when a Change Occurs


E

excel student

I have a excel workbook which contains 3 worksheets (a, b and c).

I would like to make life easy whereby "whenever a change" is made on the
source worksheets "a" and/or "b", it will automatically be reflected on
destination worksheet "c" on the designated cell locations.

The content of the "source worksheets/cells" (including cell format
definition; bold, font, border, highlight, etc) should be reflected on "c"
destination worksheet./cells.

Thank you.
 
Ad

Advertisements

F

FloMM2

excel student,
Let's say you want a change in cell "A16" on sheet"a" and /or a change in
cell "B27" on sheet"b" to show on sheet"c" in cell "A1" and cell "F72"
respectively.
On sheet "a" cell "A16", select it and select copy. On sheet "c" select cell
"A1" and select "Paste". Do the same on sheet "b" cell "B27" and again on
sheet "c" cell "F72".
In cell "A16" on sheet "c" will look something like the following:
"=sheet"c"!$A$16" (without the first " and last ")
In cell "F72" on sheet "c" will look something like the following:
"=sheet"b"!$B$27" (without the first " and last ")
hth
Dennis
 
Ad

Advertisements

N

Normek

The simple way to to update values from one cell to another is simply put an
= in the receiving cell and then select the copied cell and press return.
This however will not update the formats.
To do that I think you will need to write a macro.

You can copy and paste from one cell to another and this will also copy
formats. So do that and record a macro whilst you are doing so.

Then to automate this macro,open the Visual basic Editor and in the Project
Explorer pane, double click on the sheet that you are copying fron, to open
its associated macro page. click on the lefthand side dropdown box at the top
and select Worksheet. Then click the righthand dropdown box and select
Change. This will open a shell code.
Type inside this shell:

If target = application.range("c3") then 'exchange you cell "c3"
(leave a line)
End If

Then navigate back to the module that contains your recorded macro, and copy
the code between the first and last lines and paste it into the new macro
that you have created, between If and End if.

This will now work.

you can add an embellishment to stop the macro from flickering
Application.ScreenUpdating = False 'As the first lin in the shell
And also :
application.range(Sheet1!"c3").select 'at the end to return you to the cell
you changed.
Your code should look something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False 'Stop screen updating whilst the code is
running
If Target = Application.Range("C3") Then
Application.Range("C3").Select
Selection.Copy
Sheets("Sheet2").Select
Application.Range("B3").Select
ActiveSheet.Paste
End If
Application.CutCopyMode = False 'exit copy mode
Sheets("Sheet1").Select
Range("C3").Select
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top