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]

You showed this as the range:
If Not Intersect(Target, sh4.Range("A12:A15")) Is Nothing Then

But then said:
I write the customer's name in cell A11,
A11 is not in the range so nothing will happen

 

[My Aswer Is This]

Can you just explain in words what you wanting to do. I never like looking at a users script and then try to modify it to do what they now want
So a sheet change event normally runs when a user enters a value into a particular cell
So if I enter "Cake" into range("A1") do this

Or it could be if I enter a value into any cell in column A do this.

 

[ame21]

("A11") is a drop-down list that I created by VBA.

Range("A12:A15") in the Invoice sheet is fed from the Customer sheet that I created by Application.VLookup.

So I need to change the sheet("Invoice").Range("A12:A15") whenever I need to, but depends on the customer's name in ("A11")

 

[My Aswer Is This]

("A11") is a drop-down list that I created by VBA.

Range("A12:A15") in the Invoice sheet is fed from the Customer sheet that I created by Application.VLookup.

So I need to change the sheet("Invoice").Range("A12:A15") whenever I need to, but depends on the customer's name in ("A11")

But again when you change a Value in ("A11") which is from a drop down list what do you want to happen. And A11 drop down is in sheet named Invoice and the values in range("A12:A15") are in sheet name Invoice is this correct.
And are you saying you then want all this data in (A12:A15) copied to all the sheets in your workbook.

 

[ame21]

But again when you change a Value in ("A11") which is from a drop down list what do you want to happen. And A11 drop down is in sheet named Invoice and the values in range("A12:A15") are in sheet name Invoice is this correct.
And are you saying you then want all this data in (A12:A15) copied to all the sheets in your workbook.

Sorry, my explanation wasn't complete.

Yes, the values in the range("A12:A15") are in the sheet name Invoice that I call from the customer sheet. Now I want to copy any of this customer info that needs to be changed, to the customer sheet again.

I hope I didn't confuse you.

 

[My Aswer Is This]

I understand first question.
But when you say: that I call from the customer sheet
What does call mean? do you mean this is the sheet with the sheet change event

And the change event script should only refer to one range it seems to me. If I enter a value in A11 do this.

 

[My Aswer Is This]

In your original post you say:
when I write the customer's name in cell A11
the rest of the customer's information comes up in A12 till A15

OK I understand this.
But your sheet change event script shows:
If Not Intersect(Target, sh4.Range("A12:A15")) Is Nothing Then
This means if any value is entered into the range "A12:A15") do something
So the minute you enter a value into any of these cells the script will run

 

[ame21]

I understand first question.
But when you say: that I call from the customer sheet
What does call mean? do you mean this is the sheet with the sheet change event

And the change event script should only refer to one range it seems to me. If I enter a value in A11 do this.

I understand first question.
But when you say: that I call from the customer sheet
What does call mean? do you mean this is the sheet with the sheet change event

And the change event script should only refer to one range it seems to me. If I enter a value in A11 do this.

I understand first question.
But when you say: that I call from the customer sheet
What does call mean? do you mean this is the sheet with the sheet change event

And the change event script should only refer to one range it seems to me. If I enter a value in A11 do this.

"do you mean this is the sheet with the sheet change event" - Yes, as I said before: Range("A12:A15") in the Invoice sheet is filled out from the Customer sheet by Application.VLookup.
When I enter a customer's name in A11, I can see this customer' info (Address, Town, Email, Tel) in Range("A12:A15")

 

[My Aswer Is This]

OK you said: When I enter a customer's name in A11
So why is you sheet change event range
If Not Intersect(Target, sh4.Range("A12:A15")) Is Nothing Then
It should be A11 only