Excel solution Closed

Question

I have one Master Sheet, sheet1 in excel 2007 where I have entries of customer details from mixed cities 

Code   ContactPerson   Company    Add1   Add2   Add3   AreaCode     City   

I have made different  sheets in the same file City wise.

I am looking for a solution that the moment I enter in the MasterSheet 
all the details and as soon as I enter the city name and give enter it should automatically copy all the details in that City named sheet. 

The Master Sheet will have record of mixed cities.

TrowaD · Answer

Hi Eexpert,

Here you go:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("H")) Is Nothing Or Target.Value = vbNullString Then Exit Sub
On Error GoTo Message
Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Exit Sub
Message:
MsgBox ("Please check if you made a sheet named after this city or" & Chr(10) & "for typo's and try again.")
End Sub
Please implement code by right-clicking the sheets' tab and pasting the code in the big white field.

The code will only run when something is entered (not when something is removed) in column H.
If the city doesn't have it's own sheet yet, a message will display.

Make sure column A contains data when entering city in the respective row. (let me know if this is an issue)

Hope you like.

Best regards,
Trowa

Excel solution

1 response

Similar discussions

Subscribe To Our Newsletter!