Sending rows to another worksheet based on value of cells in column A

[Lindsey M]

:::Sorry for the duplicate post but didn't receive a reply so am trying
again ):::

Hi everyone

I have a worksheet that has 7 colmuns worth of data (it relates to team
managers, of which there are approx 10 (name stored in column A) and other
info regarding there team members.
Each team manager is part of a service, e.g. Joe Bloggs is part of Revenues
so I want all the rows that contain Joe Bloggs in column A to be transferred
to the worksheet named Revenues.
This needs to be done by code, by poss using a Command Button?
Any ideas on this?

As always, help is extremely appreciated!!

Cheers
Lindsey

 

[Guest]

Is one of the columns the service? How will the program know which sheet to
transfer the person to?

 

[Lindsey Martin]

Hi Jim

The service is not one of the columns on the Data sheet, however, on
another sheet TBR2, i have all the Team Managers listed for a find and
replace procedure that I use. This could be easily amended so that
column B is the service.

This is the code I use for the find and replace

Sub Find_Replace_TM()

Application.ScreenUpdating = False

Dim range1 As String
Dim bottomrow As Integer
bottomrow = ActiveSheet.Range("B65536").End(xlUp).Row

range1 = "B1:B" & bottomrow

Dim range2 As Range
Dim ws As Worksheet, fnd As Range, x%

Set range2 = ActiveSheet.Range(range1)
Set ws = Worksheets("TBR2") 'checks the fields to remove on Sheet2
Set fnd = ws.Range(ws.Range("A1"), ws.Range("A65536").End(xlUp))

For x = 1 To fnd.Cells.Count
range2.Replace What:=fnd(x), Replacement:="" 'replace with blanks
Next

'delete the now empty rows
Columns("B:B").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

Application.ScreenUpdating = True

End Sub

I'm assuming this could be easily amended but as I am quite new to VBA
and I "borrowed" the above off the web and amended to fit my needs, I'm
a little stuck as to where to start

Any idea on this?

Cheers
Lindsey

 

[Guest]

Instead of using a find and replace you can just use a Vlookup formula. That
is by far the easiest way to attach the service to the name. If you are
ammenable to this then give that a whirl and put the service in the 8th
column. This will make things a whole pile easier. Once that is done this can
be accomplished either with code or with a pivot table (possibly). Let me
know what you think...

 

[Spreadsheet Solutions]

Lindsey;

You can use code like this.

Do Until IsEmpty(ActiveCell)
If ActiveCell.Offset(0, j).Text = x Then
ActiveCell.EntireRow.Copy
Worksheets("Print").Range("B5").Offset(i, -1)
' -1 to set the pointer on Column A, otherwise copying will fail.
i = i + 1
End If
ActiveCell.Offset(1, 0).Select
Loop

What it does.
It starts in B5 on a certain worksheet.
Untill an empty cell is found in column B, it looks in the next cell to see
if that cellcontents matches x,
where x is some text.
If it does so, the whole row is copied to a worksheet called Print starting
in B5, making sure that the starting position
is the first column so that the whole row fits.

Mark.