If ... then insert to other worksheet

[Guest]

Objectives:
1. Go to job Name column and take the Jobs pre-fixed with either Client or
CMJ,
then insert the rows to a new Worksheet - "Client Marketing"
2. Go to job Name column and take the Jobs NOT pre-fixed with either Client
or CMJ,if FM No = 0 then insert the rows to a new Worksheet - "No FM No"

In my code I have Job Name Col as Col 2 but it does change, need the code to
adjust - many thanks for help


Sub jo()
Dim i As Variant

j = 1
For Each i In Worksheets("Jobs 0104").Cells(i, 2)
If Left(Cells(i, 2), 6) = "Client" Or Left(Cells(i, 2), 3) = "CMJ" Then

Cells(i, 2).EntireRow Insert
Destination = Worksheets("client marketing").Cells(j, 1)

If Left(Cells(i, 2), 6) <> "Client" Or Left(Cells(i, 2), 3) <> "CMJ" Then
If Cells(i, 8) = 0 Then
Cells(i, 8).Copy Destination = Worksheets("No FM No")



Next
Next
End Sub

 

[Tom Ogilvy]

There may be some typos, but I think you want something like this:

Change nCol to reflect the names column.
I assumed the other column (8 in your example) is nCol + 6. If not, assign
the value of that column to FMNoCol (for name column offset).

Sub jo()
Dim i As Variant
nCol = 2
FMNoCol = nCol + 6
j = 1
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Client Marketing").Delete
Worksheets("No FM No").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name _
= "Client Marketing"
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name _
= "No FM No"
j = 1
k = 1
LastRow = Worksheets("Jobs 0104") _
.Cells(Rows.Count, nCol).End(xlUp).Row
For i = 2 To LastRow
If Left(Worksheets("Jobs 0104").Cells(i, nCol), 6) = "Client" _
Or Left(Worksheets("Jobs 0104").Cells(i, nCol), 3) = "CMJ" Then

With Worksheets("Client Marketing")
Worksheets("Jobs 0104").Cells(i, nCol).Copy
Destination = .Cells(j, 1)
j = j + 1
End With

Else
If Worksheets("Jobs 0104").Cells(i, FMNoCol) = 0 Then
Worksheets("Jobs 0104").Cells(i, nCol).Copy _
Destination = Worksheets("No FM No").Cells(k, 1)
k = k + 1
End If
End If

Next
End Sub