Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Source Cells to Destination Cells Only when a Change Occurs
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Source Cells to Destination Cells Only when a Change Occurs
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 "excel student" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Source Cells to Destination Cells Only when a Change Occurs
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel-how to link source workbook to copy of destination workbook | Excel Worksheet Functions | |||
formulas do not copy to destination cells, but as existing totals | Excel Worksheet Functions | |||
how?data from an ext. source to copy directly int assingned cells | Setting up and Configuration of Excel | |||
i change a source and the other cells wont change | Excel Worksheet Functions | |||
Copy cells into range of cells until cell change | Excel Worksheet Functions |