VBA code to copy specified sequential cells from Sheet1 row and create new row in Sheet2 when drop down triggered

[handoverhammer]

Hello Excel Wizards,

I have been experimenting with this all week, but I continue to get errors. Any help would be appreciated.

There are two sheets, 1 and 2. Sheet1 Column A is a drop down containing the value "New". When "New" is selected, I need values from Columns B:E in that row to be copied into Sheet2, creating a new row with the data from Columns B:E.

I have found examples for copying the entire row, but I only need the data from Columns B:E.

Thanks in advance!

 

[handoverhammer]

Sorry, I forgot to clarify the target in Sheet2.

The new rows can be created beginning at A5 in Sheet2, and would paste A:D.

 

[handoverhammer]

I think I figured it out. Destination sheet is named "Sandbox"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nR As Long
Set Target = Target.Cells(1, 1)
If Not Intersect(Target, Range("A:D")) Is Nothing Then
If Target.Value = ("New") Then
nR = WorksheetFunction.Max(2, Sheets("Sandbox").Range("A" & Rows.Count).End(xlUp).Row + 1)
Range("A5", "D5").Copy Destination:=Sheets("Sandbox").Range("A" & nR)
End If
End If
Application.ScreenUpdating = False
Sheets("Sandbox").Select


End Sub

 

[handoverhammer]

Nevermind. It still is not copying the correct info.

 

[Fluff]

Hi & welcome to MrExcel
How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Target.Column = 1 Then Exit Sub
   If Target.Value = "New" Then
      Intersect(Target.EntireRow, Range("B:E")).Copy Sheets("Sandbox").Range("A" & Rows.Count).End(xlUp).Offset(1)
   End If
   Sheets("Sandbox").Select
End Sub

 

[handoverhammer]

Hi & welcome to MrExcel
How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Target.Column = 1 Then Exit Sub
   If Target.Value = "New" Then
      Intersect(Target.EntireRow, Range("B:E")).Copy Sheets("Sandbox").Range("A" & Rows.Count).End(xlUp).Offset(1)
   End If
   Sheets("Sandbox").Select
End Sub

That is better! Thanks Fluff.

However, there are 3 other possible selections in the drop down (active, won, dead). If any of those are selected in Sheet1, the view still moves to "Sandbox".

Can we limit the screen change to only "new" being selected and exclude the other three?

 

[Fluff]

That's what this art should do

If Target.Value = "New" Then
      Intersect(Target.EntireRow, Range("B:E")).Copy Sheets("Sandbox").Range("A" & Rows.Count).End(xlUp).Offset(1)
   End If

If target is not "New" nothing should happen

 

[handoverhammer]

It appears the issue was here:

End If
Sheets("Sandbox").Select

I moved the Sheets Select command above the End If. I think because the Sheets Select was below/after the End If, it was forcing the move regardless of drop down selection.

I'm learning!

 

[handoverhammer]

One more quick question. How do I include a timestamp in Column E of the target row?

I read something about modules that trigger off the macro and came up with whats below, but it did nothing ...

Sub Macro()
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Target.Column = 1 Then Exit Sub
If Target.Value = "New" Then
Intersect(Target.EntireRow, Range("B:E")).Copy Sheets("Sandbox").Range("A" & Rows.Count).End(xlUp).Offset(1)
Sheets("Sandbox").Select
End If
Sheets("Sandbox").[Target.Column, "E"] = Now
End Sub

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Target.Column = 1 Then Exit Sub
   If Target.Value = "New" Then
      With Sheets("Sandbox").Range("A" & Rows.Count).End(xlUp)
         Intersect(Target.EntireRow, Range("B:E")).Copy .Offset(1)
         .Offset(1, 5) = Now
         Sheets("Sandbox").Select
      End With
   End If
End Sub