Help with vba to activate on input of cell

[A Thayuman]

Hi I hope that I can be helped with using activate on input cell

'Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Traget, Range("A2:D2")) Is Nothing Then

Sheets("Sheet1").Select
Range("A2:D2").Select
Selection.UnMerge
Range("A2").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Activate
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select

ActiveSheet.Paste

Sheets("Sheet1").Select
Range("A2:D2").Select
Selection.Merge

End If

End Sub

The current macro as see below works fine without adding this feature however when I use the above and when it is in module 1
Sub test()
'Copy Invoice Number to Data Sheet

Sheets("Sheet1").Select
Range("A2:D2").Select
Selection.UnMerge
Range("A2").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Activate
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select

ActiveSheet.Paste

Sheets("Sheet1").Select
Range("A2:D2").Select
Selection.Merge
End Sub

Mr.xlsm
	A	B	C	D	E	F	G	H
1	Customer ID				Customer Name
2	126				Jack Garret
Sheet1

Mr.xlsm
	A	B
1	Customer ID	Customer Name
2	123	Jack Garret
3	123
4	123
Sheet2

 

[Skyybot]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Range("A2").Value = "" Then
Sheets("Sheet1").Select
Range("A2:D2").Select
Selection.UnMerge
Range("A2").Select
Selection.Copy
Sheets("Sheet2").Select
Sheets("Sheet2").Range("A1").Activate
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("A2:D2").Select
Selection.Merge
End If
End Sub

 

[smozgur]

@A Thayuman: I removed the solution mark from your own post since the provided code in Post #12 was already the correct implementation except it works on every single worksheet_change event - which you already realized that it is not the efficient way of solving the problem.

With your latest follow-up question, @Skyybot already provided a much better approach which is checking the A2 cell and only executing if the cell is empty, so Post #21 is the answer to the question as I can see as I would mark it as the solution to help future readers.

 

[A Thayuman]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Range("A2").Value = "" Then
Sheets("Sheet1").Select
Range("A2:D2").Select
Selection.UnMerge
Range("A2").Select
Selection.Copy
Sheets("Sheet2").Select
Sheets("Sheet2").Range("A1").Activate
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("A2:D2").Select
Selection.Merge
End If
End Sub

Thanks Skybolt works perfectly

 

[A Thayuman]

@A Thayuman: I removed the solution mark from your own post since the provided code in Post #12 was already the correct implementation except it works on every single worksheet_change event - which you already realized that it is not the efficient way of solving the problem.

With your latest follow-up question, @Skyybot already provided a much better approach which is checking the A2 cell and only executing if the cell is empty, so Post #21 is the answer to the question as I can see as I would mark it as the solution to help future readers.

Thanks I have a better understanding of why the if, then and end if is required