Previous Topic Next Topic

Excel solution

Closed
eexpert Posts 2 Registration date Friday June 7, 2013 Status Member Last seen June 28, 2013 - Jun 7, 2013 at 08:51 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 10, 2013 at 11:52 AM
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.
Related:

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jun 10, 2013 at 11:52 AM
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

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
Is there any solution for this problem?
Salman -
HelpiOS -

1 response
Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses