How to copy the rows into different tabs based on string in group of strings?

[Bhargavaj]

I need a macro to copy the rows from "Data" sheet to into different tabs based on string in group of strings in a particular cell. For example: in address column i have apt no street and city name is there...I want to copy the rows based on city name(mumbai)into different tab.

No Name Address

1 Sachin H.No:123 Thane Mumbai

2 Dhoni Building 456 MG road Bangalore

3 Gambir H.No: 789 Main Road Mumbai

4 Dravid Apt 123 Kings road bangalore

 

[Luke M]

First, welcome to the forums!


The first issue would be to break up the address cell into 2 columns, 1 for street and 1 for city. Assuming the city is always the last word in cell, formula is:

=TRIM(MID(SUBSTITUTE(C2," ",REPT(" ",999),LEN(C2)-LEN(SUBSTITUTE(C2," ",""))),999,999))


As to the latter question, I'd first question why you want to split your data? Typically we want to have all the data in one place so that it's easier to change/analyze. Perhaps consider using either a PivotTable with City as PageField criteria?


If you insist, the easiest way would be to put an AutoFilter on the main sheet, filter for a city, then copy the results out. You could record yourself doing that to get the basic macro structure.

 

[Bhargavaj]

Thanks Luke for quick response. May be your formuls would be good if I have only address. But basically I am looking VBA code for copying row into different tab if one cell contains required string in group of strings. For example:

No Name Comments

1 Sachin hey buddy! Good work. Keep it.

2 Dhoni Wish you happy birthday boss

3 Gambir Buddy let us go!!

4 Dravid I am leaving to home boss!

I need to search comments column, if the cell contains "buddy", i need to copythe entire row into another tab called buddy in the same workbook.So Buddy tab contains 1 and 3rd rows. Just i gave sample rows above. I looking for macro because i have thousand of lines like that.

 

[Luke M]

How's this?

[pre]

Option Compare Text
Sub CopyRows()
Dim MyWord As String
Dim xRow As Integer
Dim Exists As Boolean
Dim xCount As Integer
Dim MyCol As String

Application.ScreenUpdating = False
'I'll assume we're looking in col B of Data sheet
MyCol = "B"

xRow = Worksheets("Data").Cells(65536, MyCol).End(xlUp).Row

MyWord = InputBox("What word are you looking for?", "Search for...")
If MyWord = "" Then Exit Sub 'No value given

Exists = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name = MyWord Then
Exists = True
Exit For
End If
Next ws

'Create new sheet
If Not (Exists) Then
Sheets.Add
ActiveSheet.Name = MyWord
End If
Worksheets(MyWord).Cells.Delete

'Begin copying data
xCount = 1
With Worksheets("Data")
For i = 1 To xRow
If .Cells(i, MyCol) Like "*" & MyWord & "*" Then
.Cells(i, MyCol).EntireRow.Copy Worksheets(MyWord).Cells(xCount, "A")
xCount = xCount + 1
End If
Next i
End With

Application.ScreenUpdating = True

End Sub

[/pre]

 

[Bhargavaj]

Pefect Luke!! Thanks a Lot...