Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending rows to another worksheet based on value of cells in column A
:::Sorry for the duplicate post but didn't receive a reply so am trying
again :o)::: 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending rows to another worksheet based on value of cells in colum
Is one of the columns the service? How will the program know which sheet to
transfer the person to? "Lindsey M" wrote: :::Sorry for the duplicate post but didn't receive a reply so am trying again :o)::: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending rows to another worksheet based on value of cells in colum
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 *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending rows to another worksheet based on value of cells in c
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... "Lindsey Martin" wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending rows to another worksheet based on value of cells in column A
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. -- Rosenkrantz Spreadsheet Solutions Witkopeend 24 1423 SN Netherlands ------------------------ E: W: www.rosenkrantz.nl ------------------------ "Lindsey M" wrote in message ... :::Sorry for the duplicate post but didn't receive a reply so am trying again :o)::: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending rows to another worksheet based on value of cells in column A
Mark, Pretty new to this whole thing but when i tried your code i seem to ge a syntax error. Could you point out where the problem is please? Thanks, Chri -- chris10 ----------------------------------------------------------------------- chris100's Profile: http://www.excelforum.com/member.php...fo&userid=2516 View this thread: http://www.excelforum.com/showthread.php?threadid=36166 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum specific column rows based on 2 different column criteria | Excel Worksheet Functions | |||
Display cells(text) in one column based on cells which are present inother column | Excel Discussion (Misc queries) | |||
How do I prevent rows from duplicating when sending a worksheet? | Excel Worksheet Functions | |||
Many Rows to One Row based off of first column | Excel Programming | |||
Sending data in certain cells to another worksheet by pressing a button | Excel Programming |