Hi

I am looking for VB codes to copy rows based on partial cell content.

I have a spreadsheet called "main.xls" from which I would like to copy data
to another spreadsheet when certain crietria are met.

Column I have following data.
TML123
TML702
TML4568
TML956
FTF987
FTF0956
FTF687
TML257

I would like the macro to copy rows that have TML prefix to another workbook
(After.xls) and paste under a tab named TML. The macro should also copy rows
that have FTF prefix to the same workbook (After.xls) and paste under a tab
named FTF.

Thanks

as a starter for 10 ...lets use the filter to make it easier. this extracst
the data to two new worksheets. All you need to do is copy the data.

Option Explicit
Sub FilterData()
Extract "TML"
Extract "FTF"
End Sub
Sub Extract(what As String)
Dim ws As Worksheet
Set ws = Worksheets.Add
ws.Range("A1") = "AAA"
ws.Range("D1") = "AAA"
ws.Range("D2") = what & "*"

Sheets("Sheet1").Range("A1:A1000").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=ws.Range("D1:D2"), CopyToRange:=ws.Range("A1"),
Unique:=False

'To DO
' Copy the data from ws to wherever you want

End Sub



"Nik" wrote:

Hi

I am looking for VB codes to copy rows based on partial cell content.

I have a spreadsheet called "main.xls" from which I would like to copy data
to another spreadsheet when certain crietria are met.

Column I have following data.
TML123
TML702
TML4568
TML956
FTF987
FTF0956
FTF687
TML257

I would like the macro to copy rows that have TML prefix to another workbook
(After.xls) and paste under a tab named TML. The macro should also copy rows
that have FTF prefix to the same workbook (After.xls) and paste under a tab
named FTF.

Thanks

Try the below macro from Main xls activesheet

Sub Macro()

Dim wb As Workbook, lngRow As Long, lngNextRow As Long
Set wb = Workbooks("after.xls")

For lngRow = 1 To ActiveSheet.Cells(Rows.Count, "I").End(xlUp).Row
If Range("I" & lngRow) Like "TML*" Or Range("I" & lngRow) Like "FTF*" Then
lngNextRow = wb.Worksheets(CStr(Left(Range("I" & lngRow), 3))).Cells( _
Rows.Count, "I").End(xlUp).Row + 1
Rows(lngRow).Copy _
wb.Worksheets(CStr(Left(Range("I" & lngRow), 3))).Rows(lngNextRow)
End If
Next

End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Nik" wrote:

Hi

I am looking for VB codes to copy rows based on partial cell content.

I have a spreadsheet called "main.xls" from which I would like to copy data
to another spreadsheet when certain crietria are met.

Column I have following data.
TML123
TML702
TML4568
TML956
FTF987
FTF0956
FTF687
TML257

I would like the macro to copy rows that have TML prefix to another workbook
(After.xls) and paste under a tab named TML. The macro should also copy rows
that have FTF prefix to the same workbook (After.xls) and paste under a tab
named FTF.

Thanks

Thanks Patrick.

But I was hoping if i could automate the whole process and that includes the
coping and pasting to new workbook.

The main.xls file is open and the macro is located in after.xls

The codes you provided will filter and then copied manually to the
destination tabs. Any help will be appreciated.

"Patrick Molloy" wrote:

as a starter for 10 ...lets use the filter to make it easier. this extracst
the data to two new worksheets. All you need to do is copy the data.

Option Explicit
Sub FilterData()
Extract "TML"
Extract "FTF"
End Sub
Sub Extract(what As String)
Dim ws As Worksheet
Set ws = Worksheets.Add
ws.Range("A1") = "AAA"
ws.Range("D1") = "AAA"
ws.Range("D2") = what & "*"

Sheets("Sheet1").Range("A1:A1000").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=ws.Range("D1:D2"), CopyToRange:=ws.Range("A1"),
Unique:=False

'To DO
' Copy the data from ws to wherever you want

End Sub



"Nik" wrote:

Hi

I am looking for VB codes to copy rows based on partial cell content.

I have a spreadsheet called "main.xls" from which I would like to copy data
to another spreadsheet when certain crietria are met.

Column I have following data.
TML123
TML702
TML4568
TML956
FTF987
FTF0956
FTF687
TML257

I would like the macro to copy rows that have TML prefix to another workbook
(After.xls) and paste under a tab named TML. The macro should also copy rows
that have FTF prefix to the same workbook (After.xls) and paste under a tab
named FTF.

Thanks