Excel VBA, How to Copy the cell/cells from sheet1 to the sheet2 with condition

[ame21]

I have two sheets, invoice, and customer. On the invoice sheet, when I write the customer's name in cell A11, the rest of the customer's information comes up in A12 till A15. However, I want to change this information from the invoice sheet and copy it on the customer sheet. My code works for a customer. But I need this code for every customer. I need your idea, please

Private Sub Worksheet_Change(ByVal Target As Range)

    Set sh1 = ThisWorkbook.Sheets("Customer")

    Set sh4 = ThisWorkbook.Sheets("Invoice")   

    If Not Intersect(Target, sh4.Range("A12:A15")) Is Nothing Then

        If sh4.Range("A11").Value = sh1.Range("B2").Value Then

            sh1.Range("F2").Value = sh4.Range("A12").Value

            sh1.Range("G2").Value = sh4.Range("A13").Value

        End If

    End If

   End Sub

 

[My Aswer Is This]

Here is a script where if you enter a sheet name in Range("A1")
The Range("A12:A14") will be copied to the same range on the sheet name you entered in Range("A1") on the sheet with the change event code.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  10/10/2021  2:58:58 PM  EDT
If Target.Address = "$A$1" Then
On Error GoTo M
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
    
    Range("A12:A14").Copy Sheets(Target.Value).Range("A12:A14")
End If
Range(Target.Address).Select
Exit Sub
M:

MsgBox "Sheet named  " & Target.Value & vbNewLine & "Does Not Exist"
End Sub

 

[ame21]

Here is a script where if you enter a sheet name in Range("A1")
The Range("A12:A14") will be copied to the same range on the sheet name you entered in Range("A1") on the sheet with the change event code.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  10/10/2021  2:58:58 PM  EDT
If Target.Address = "$A$1" Then
On Error GoTo M
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
   
    Range("A12:A14").Copy Sheets(Target.Value).Range("A12:A14")
End If
Range(Target.Address).Select
Exit Sub
M:

MsgBox "Sheet named  " & Target.Value & vbNewLine & "Does Not Exist"
End Sub

Thanks for your script, but when I run it my excel crashes

 

[My Aswer Is This]

Remove this line of code
Range(Target.Address).Select

 

[ame21]

With your script, I'm not able to change the value in Range ("A12:A15") before copy

Option Explicit

Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh4 As Worksheet

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$A$11" Then
    On Error GoTo M
    If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
      
        Range("A12:A14").Copy Sheets(Target.Value).Range("A12:A14")
    End If
    Range(Target.Address).Select
    Exit Sub
M:
    
    MsgBox "Sheet named  " & Target.Value & vbNewLine & "Does Not Exist"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Call lookup
End Sub

Sub lookup()

    Dim srchres As Variant
    Dim srch As Variant
    Dim lookUpRange As Range

    Set sh1 = ThisWorkbook.Sheets("Customer")
    Set sh4 = ThisWorkbook.Sheets("Invoice")
    Set lookUpRange = sh4.Range("A11:C11")
    
    'Address
    On Error Resume Next
    srchres = Application.VLookup(lookUpRange, _
    sh1.Range("B2:H999999"), 5, False)
    On Error GoTo 0
    If (IsEmpty(srchres)) Then
      sh4.Range("A12:C12") = "" 'CVErr(xlErrNA)
    Else
      sh4.Range("A12:C12").Value = srchres
    End If
    
    'Town
     On Error Resume Next
    srch = Application.VLookup(lookUpRange, _
    sh1.Range("B2:H999999"), 6, False)
    On Error GoTo 0
    sh4.Range("A13:C13").Value = srch
    
    'postcode
     On Error Resume Next
    srch = Application.VLookup(lookUpRange, _
    sh1.Range("B2:H999999"), 7, False)
    On Error GoTo 0
    sh4.Range("A14:C14").Value = srch
    
    'Telephone
     On Error Resume Next
    srch = Application.VLookup(lookUpRange, _
    sh1.Range("B2:H999999"), 3, False)
    On Error GoTo 0
    sh4.Range("A15:C15").Value = srch
    
    'Clear Customer Info
    On Error Resume Next
    If sh4.Range("A11") = "" Then
        sh4.Range("A12:C12").Value = ""
        sh4.Range("A13:C13").Value = ""
        sh4.Range("A14:C14").Value = ""
        sh4.Range("A15:C15").Value = ""
    End If

End Sub

 

[My Aswer Is This]

You said:
With your script, I'm not able to change the value in Range ("A12:A15") before copy
My script only runs when you enter a sheet name in Range("A1")
So not sure why you do not change the values before copy.
And I see you have a lot more code here in this sheet then I gave you.
So I have no way of knowing what else your doing with all this other code.
Having more then one sheet change event code in same sheet can be tricky.
You never mentioned having all this other code in your sheet.

 

[ame21]

You said:
With your script, I'm not able to change the value in Range ("A12:A15") before copy
My script only runs when you enter a sheet name in Range("A1")
So not sure why you do not change the values before copy.
And I see you have a lot more code here in this sheet then I gave you.
So I have no way of knowing what else your doing with all this other code.
Having more then one sheet change event code in same sheet can be tricky.
You never mentioned having all this other code in your sheet.

Ok thanks for your help