Copying Cell Value to another sheet in Excel with VBA

Question

I have the following code which successfully copies cells B, E and F to cells B, C, D in sheet 2 after I match a string in another cell. The problem is that it copies the cell and not only just the value inside it (I don't need borders, colour etc).

Another issue I have is that while it will copy the data to the next free row in Column B, it won't look for the next free row according to column C and D.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim thisrow As Long
Dim lr As Long
If Target.Column = 7 Then
    thisrow = Target.Row
If Target.Value = "FAULTY" Then
    lr = Sheets("sheet2").Range("B" & Rows.Count).End(xlUp).Row + 1
    Range("B" & ActiveCell.Row).Copy Sheets("sheet2").Range("B" & lr)
    Range("D" & ActiveCell.Row).Copy Sheets("sheet2").Range("C" & lr)
    Range("F" & ActiveCell.Row).Copy Sheets("sheet2").Range("D" & lr)
End If
End If

End Sub

use `Range(..).Value = Range(..).Value` instead `Range(..).Copy` — Dmitry Pavliv, Apr 24 '14 at 19:27
Paste special is another option. http://msdn.microsoft.com/en-us/library/office/ff839476(v=office.15).aspx — RubberDuck, Apr 24 '14 at 19:31
@ckuhn203 Yeah, but `.select` needs to be activated in order to use pastespecial, and select is also know as a bad programming practice http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select — Rich, Apr 24 '14 at 19:35
@Rich, `but .select needs to be activated in order to use pastespecial,` - why do you think so? `Range("A1").Copy` and then `Sheets("sheet2").Range("B2").PasteSpecial xlPasteValues` — Dmitry Pavliv, Apr 24 '14 at 19:37

Rich · Accepted Answer · 2014-04-24T19:59:03.490

You can use the .Value operator instead. Also, just set a separate variable for the C/D Range for the next available cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim thisrow As Long
    Dim lr As Long
    If Target.Column = 7 Then
        thisrow = Target.Row
        If Target.Value = "FAULTY" Then
            lr = Sheets("sheet2").Range("B" & Rows.Count).End(xlUp).Row + 1
            mr = Sheets("sheet2").Range("C" & Rows.Count).End(xlUp).Row + 1
            nr = Sheets("sheet2").Range("D" & Rows.Count).End(xlUp).Row + 1
            Sheets("sheet2").Range("B" & lr).Value = Range("B" & ActiveCell.Row).Value
            Sheets("sheet2").Range("C" & mr).Value = Range("D" & ActiveCell.Row).Value
            Sheets("sheet2").Range("D" & nr).Value = Range("F" & ActiveCell.Row).Value
        End If
    End If
End Sub

Hi Rich, the data in the target row actually disappears and doesn't go anywhere. Not entirely sure whats happened there! — user3569697, Apr 24 '14 at 19:50
@user3569697 It seems I got it backwards, excuse my dyslexia :) I Updated the code above, give that a shot. — Rich, Apr 24 '14 at 19:59

Copying Cell Value to another sheet in Excel with VBA

1 Answers1