Excel solution

Closed
Report
Posts
2
Registration date
Friday June 7, 2013
Status
Member
Last seen
June 28, 2013
-
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
-
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.

1 reply

Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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
0