VBA - Copy/Move value from cell A1 to A2 when cell A1 is changed.

Rob5060

New Member
Joined
Nov 29, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
For starters I'm very new to VBA. I'm attempting to update an overtime log that we use at work to track everyone's overtime in order to make it more efficient. So far I have some VBA code in the worksheet that will sort the employees by the date they worked overtime as well as by their seniority. What I'm trying to figure out now is there a way to move the value in one cell to another when the original cell is updated. I've posted a picture of an example worksheet for reference, but what I would like for it to do is take the date that's in C5 and copy/move it to D5 when the date in C5 is changed/updated, and do it for each row when any one cell in column C is changed.

I have been searching all over for ways/ideas on how to do this but haven't been able to come up with anything. I'm also wondering is there a way to be able to copy and paste values from one cell to another within the worksheet. I'm a big user of keyboard shortcuts (ctrl+c and ctrl+v) and I've been reading up on sendkeys. Is there a way to program sendkeys to be able to use the keyboard shortcuts again or any other convenient options?

I've also included the code I'm currently using in my worksheet. Any help on this is appreciated!

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("A4").CurrentRegion.Offset(1).Sort [C4], xlAscending, [A4], , xlDescending
    
  End Sub
 

Attachments

  • Example Sheet Pic.JPG
    Example Sheet Pic.JPG
    57.3 KB · Views: 20

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
For starters I'm very new to VBA. I'm attempting to update an overtime log that we use at work to track everyone's overtime in order to make it more efficient. So far I have some VBA code in the worksheet that will sort the employees by the date they worked overtime as well as by their seniority. What I'm trying to figure out now is there a way to move the value in one cell to another when the original cell is updated. I've posted a picture of an example worksheet for reference, but what I would like for it to do is take the date that's in C5 and copy/move it to D5 when the date in C5 is changed/updated, and do it for each row when any one cell in column C is changed.

I have been searching all over for ways/ideas on how to do this but haven't been able to come up with anything. I'm also wondering is there a way to be able to copy and paste values from one cell to another within the worksheet. I'm a big user of keyboard shortcuts (ctrl+c and ctrl+v) and I've been reading up on sendkeys. Is there a way to program sendkeys to be able to use the keyboard shortcuts again or any other convenient options?

I've also included the code I'm currently using in my worksheet. Any help on this is appreciated!

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("A4").CurrentRegion.Offset(1).Sort [C4], xlAscending, [A4], , xlDescending
   
  End Sub
just use formula to do it, no need vba, but in vba, you can insert this sub to worksheet that include your table:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lr As Long
    lr = Range("C" & Rows.Count).End(xlUp).Row
    If lr < 5 Then Exit Sub
    If Not Intersect(Target, Range("C5:C" & lr)) Is Nothing Then
        Target.Offset(, 1).Value = Target.Value
    End If
End Sub
 
Upvote 0
just use formula to do it, no need vba, but in vba, you can insert this sub to worksheet that include your table:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lr As Long
    lr = Range("C" & Rows.Count).End(xlUp).Row
    If lr < 5 Then Exit Sub
    If Not Intersect(Target, Range("C5:C" & lr)) Is Nothing Then
        Target.Offset(, 1).Value = Target.Value
    End If
End Sub
Thank you for your help. That code works for changing the second cell value, but it makes it the same as the first cell when it is changed. What I'm hoping for it to do is, if cell C5 is the date of 11/8/23 and D5 is 11/6/23 and if I change C5 to say 11/10/23, then D5 would change to C5's original date. So I'd change C5 to 11/10/23 and then D5 would change from 11/6/23 to 11/8/23.
 
Upvote 0

Forum statistics

Threads
1,224,813
Messages
6,181,111
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top