VBA copy/paste specific text if target cell is not empty

  • Hi,


    I'm trying to write code ,If the offset cell is empty then no action is required otherwise copy offset cell content and paste it into another sheet and at the same time I want one more specific word to be pasted in another cell.

    So far I found the code that would only copies the offset cell and pastes it into another sheet.


  • Hello,


    Whenever you are creating an Event macro such as your example : Private Sub Worksheet_Change(ByVal Target As Range)


    ActiveCell has to be replaced by Target ....


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Use a with statement

    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. With Target
    3. If Intersect(Target, Range("D7:D130")) Is Nothing Or .Cells.CountLarge > 1 Or isempty(Target) Then Exit Sub
    4. Sheets("Sheet10").Range("I3").Value = .Offset(, -1).Value
    5. Sheets("Sheet10").Range("K3").Value = .Value
    6. Sheets("Sheet10").Range("Q3").Value = .Offset(, 1).Value
    7. End With
    8. End Sub
  • Thanks Carim for you Suggestion and thanks roy Uk for the reply


    That's Great!


    But how can perform this following activity in your suggested code?


    Code
    1. 'If .Offset(0, 7) ="" Then No action required
    2. 'Otherwise Sheets("Sheet10").Range("AJ4").Value = .Offset(0, 7).Value & also Sheets("Sheet10").Range("AJ5").Value = "South"
    3. 'If .Offset(0, 9) ="" Then No action required
    4. 'Otherwise Sheets("Sheet10").Range("AM4").Value = .Offset(0, 9).Value & also Sheets("Sheet10").Range("AM5").Value = "East"
    5. 'If .Offset(0, 12) ="" Then No action required
    6. 'Otherwise Sheets("Sheet10").Range("AP4").Value = .Offset(0, 12).Value & also Sheets("Sheet10").Range("AP5").Value = "West"
  • Try this, I haven't tested it but it should work


  • Hi roy UK

    I need more help .... How can I combine the above code prepared by you with the below code ?

    i tried alot......but receiving else if without if error...


  • I'm not sure what you want, maybe