Selective Duplication of entries into separate worksheet based on content

  • Thread starter Thread starter Paul Arnold
  • Start date Start date
P

Paul Arnold

Simple to those in the know.....

I am looking for a means (if possible!) of programming
Excel to duplicate a row (in its entirety) to another
worksheet but only if something specific is entered into a
field.

For example, I enter the word "LOW" into a cell and the
entire row that this cell appears in is replicated into
another worksheet.

Am I asking the impossible?

Grateful for any help.

Regards,

Paul.
 
Paul,

Here's some worksheet event code that does the copy if LOW is entered in
column A

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cRows As Long

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 1 Then
If Target.Count = 1 Then
If Target.Value = "LOW" Then
With Worksheets("Sheet2")
cRows = .Cells(Rows.Count, "A").End(xlUp).Row
If cRows > 1 Or .Range("A1").Value <> "" Then
cRows = cRows + 1
End If
Target.EntireRow.Copy Destination:=.Cells(cRows, "A")
End With
End If
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Okay, I'm obviously a little out of my depth asking
questions in here!

I'm glad that my scenario is solvable but short of opening
visual basic editor - I've don't have a lot of experience
in what to do with the code below.

Can you give me some tips or can I forward my workbook
with instructions if it's easier?

Any help, gratefully appreciated.

Regards,

Paul.
-----Original Message-----
Paul,

Here's some worksheet event code that does the copy if LOW is entered in
column A

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cRows As Long

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 1 Then
If Target.Count = 1 Then
If Target.Value = "LOW" Then
With Worksheets("Sheet2")
cRows = .Cells(Rows.Count, "A").End (xlUp).Row
If cRows > 1 Or .Range("A1").Value
Click to expand...
cRows = cRows + 1
End If
Target.EntireRow.Copy
Click to expand...
Destination:=.Cells(cRows, "A")
 
Paul,

I am happy for you to forward your workbook as it might be simpler given you
experience<g>.

I will send you my email address directly, as this account is currently
bouncing everything due to the amount of spam I am getting.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top