Copy/Paste Special based on value in different cell

[EssKayKay]

Hello,



I’m looking for a way to update a cell that has a formula in it to the value of the results of the formula based on the input in a different cell in the same row. In essence COPY>PASTE SPECIAL>VALUE.

As a TEST example:
Formula in Column A is =$C$3 (copied to cells A8:A20)
Column C is formatted to DATE (mm/dd/yyyy) (cells C8:C20)

If a date is entered in a cell in Column C then convert the formula’s result in Column A of the same row to the value in the same Column A cell. If there is no date entered in Column C then leave the formula (i.e., do not copy/paste).

Once copy/pasted, if the value in $C$3 changes only the cells in A8:A20 will change where there is no data (date) entered in the corresponding row/cell where column C of that row is empty (i.e., no date has been entered). I want this routine to trigger automatically once a date has been entered but if necessary, this could be updated upon exiting the spreadsheet.

This routine may have to change a bit in the future but for now let’s just look at this. I tried to attached a very limited spreadsheet showing what I have in mind but was unsuccessful - any suggestions, I'll try to repost with attached file. I am running Excel 2007.

Thanks,
Steve K.

 

[kevin9999]

Using your limited sample, the following worksheet change event code should give you what you want. Right click the sheet tab name, select View Code, and paste the code below to the window that appears on the right of screen. Save as a macro--enabled file.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("C8:C20"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        If IsDate(Target) Then Target.Offset(, -2).Value = Target.Offset(, -2).Value
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

 

[EssKayKay]

Using your limited sample, the following worksheet change event code should give you what you want. Right click the sheet tab name, select View Code, and paste the code below to the window that appears on the right of screen. Save as a macro--enabled file.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("C8:C20"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        If IsDate(Target) Then Target.Offset(, -2).Value = Target.Offset(, -2).Value
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

Thank you Kevin for your quick response.

I first tried your code on my limited "test" file and it worked perfectly. I then copied the code to my actual spreadsheet. However, since I already had a “Private Sub Worksheet_Change(ByVal Target As Range)” procedure, I was a bit concerned. I simply added your coding to the end of the Sub. I had to modify the code slightly (changing column C to M and offset to -5). It appears to be working great. I will have to do a bit more testing but it looks very promising. My Column M has 1200 rows in it so I'm hoping it doesn't bog down too much compared to the Test file with only 12 rows. I'll keep you posted.

Again my thanks - much appreciated,
Steve

 

[EssKayKay]

OOPS - double post. . .

 

[kevin9999]

Happy to help and thanks for the feedback

 

[EssKayKay]

Happy to help and thanks for the feedback

Kevin,

I hate to bother you but I just noticed one thing in my spreadsheet that does not happen in the test file. When I enter a date in my Column M and press ENTER the corresponding cell in Column H does not update (i.e., enter the value). After pressing ENTER the next cell is now highlighted. If I cursor back up to the cell in Column M where I just entered a date it does update the cell in Column H. OR another way is if I highlight the cell where I just entered a date then cursor either up or down it updates. It’s like the after entering data in a cell in Column M you must highlight that cell then cursor (not mouse) to a proceeding or following cell (i.e. either up or down).

 

[EssKayKay]

I did some more testing. All I had to do was cursor back up to the cell I just entered a date in and the cell in Column H updated. I tried adding a line
ActiveCell.Offset(-1, 0).Select
but it did not work. I'll keep trying things. Any other suggestions would be greatly appreciated.

 

[EssKayKay]

After playing with this and doing more research (Google is my friend) I think I have it working. I added the following to my “Private Sub Worksheet_Change(ByVal Target As Range)” routine.

If Not Intersect(Target, Range("M:M")) Is Nothing And Target.CountLarge = 1 Then
ActiveCell.Offset(-1, 0).Select
End If

 

[kevin9999]

Good to hear you got it working