Urgent::: Need Macro Code for Copy/Paste Values

[lesliewheeler]

Hi,

I am hoping someone can help with a VBA code that will do the following

AUTOMATICALLY
copy cell A9 and Paste Value to B9
copy cell A10 and Paste Value to B10
copy cell C9 and Paste Value to D9
copy cell C10 and Paste Value to D10

This should happen every time any values change in the worksheet.

I tried recording macros and run it but it gets into endless loop and i get error or excel crashes.

Pls also let me know how and where to place the code (module/worksheet code) and how to put it on auto run when sheet is worked on.

Thank you in advance!!!!

 

[Joe4]

How exactly are the values on that sheet changing?
If there are manually changes happening somewhere on that sheet that should trigger it to run, place this code in the proper sheet module (one way to make sure you put it in the right place is to go to the sheet, right-click on the sheet tab name at the bottom of the screen, select "View Code" and paste this code in the resulting VB Editor window):

Private Sub Worksheet_Change(ByVal Target As Range)

'   Temporarily disable events while make updates
    Application.EnableEvents = False

'   Make updates
    Range("B9").Value = Range("A9").Value
    Range("B10").Value = Range("A10").Value
    Range("D9").Value = Range("C9").Value
    Range("D10").Value = Range("C10").Value
    
'   Reable events
    Application.EnableEvents = False
    
End Sub

If the values are changing via another means, please explain that to us.

Also, if you don't mind, please stop including the word "Urgent" in all your titles. That isn't really necessary, and isn't going to speed up the process of getting replies. Many people are actually turned off by that, and ignore such posts, so it probably isn't helping you get your replies any faster.

 

[cooper645]

Does it have to be a macro? Could you not just just use =$A$9 in cell B9 and so on?

 

[Eric W]

Try this:

Open a copy of your workbook. Right click on the sheet tab on the bottom and select View Code. Paste the following into the window that opens:

Private Sub Worksheet_Change(ByVal target As Range)

    If Intersect(target, Range("A9:A10,C9:C10")) Is Nothing Then Exit Sub
    target.Offset(, 1).Value = target.Value
End Sub

Press Alt-Q to close the VBA editor.

That's it. Now anything you put in A9, A10, C9, or C10 will be automatically copied to the cell on the right.

This is a slight variation on Joe4's macro, it assumes that those cells are changed manually.

 

[Joe4]

This is a slight variation on Joe4's macro, it assumes that those cells are changed manually.

I was thinking that they might be formulas referencing other cells on the same sheet. My solution would work in either instance.
The only instance in which they would not work is if those cells are pulling data from other sheets or sources.

 

[lesliewheeler]

How exactly are the values on that sheet changing?
If there are manually changes happening somewhere on that sheet that should trigger it to run, place this code in the proper sheet module (one way to make sure you put it in the right place is to go to the sheet, right-click on the sheet tab name at the bottom of the screen, select "View Code" and paste this code in the resulting VB Editor window):

Private Sub Worksheet_Change(ByVal Target As Range)

'   Temporarily disable events while make updates
    Application.EnableEvents = False

'   Make updates
    Range("B9").Value = Range("A9").Value
    Range("B10").Value = Range("A10").Value
    Range("D9").Value = Range("C9").Value
    Range("D10").Value = Range("C10").Value
    
'   Reable events
    Application.EnableEvents = False
    
End Sub

If the values are changing via another means, please explain that to us.

Also, if you don't mind, please stop including the word "Urgent" in all your titles. That isn't really necessary, and isn't going to speed up the process of getting replies. Many people are actually turned off by that, and ignore such posts, so it probably isn't helping you get your replies any faster.

Thank you for the response Joe... Am trying it out... Apologies if "Urgent" Title ticked you off but thr fact is i needed it urgently as I needed to finish this and go off to sleep

I will take care from next time..

Appreciate your support

 

[lesliewheeler]

Hi Joe,

The code didnt work... the value in A9,A10 are dynamically changing as per the data recd from the below array formula
="J"&MAX(IF(J4:J3001="LLL",ROW(J4:J3001)-MIN(ROW(J4:J3001))+4))

I would like the copy paste values to happen whenever there is a change in the A9,A10 Cells.

PLs do help

 

[Joe4]

Apologies if "Urgent" Title ticked you off but thr fact is i needed it urgently

Its not that it ticked me off (though your last three threads all have it). I saw one earlier, and my thinking was I didn't have the time to commit to turn it around fast, so I just bypassed it, leaving it for someone else. Some people do get ticked off by it though, as I have seen from past conversations.

Regarding your question, why wouldn't just using formula references to those cells like Cooper645 suggested work? That would be immediate. Seems like VBA may be unnecessary here.