Copying matched criteria plus relevant columns to new worksheet

I want to use code in a macro to search for criteria in a worksheet which 
allows the matched data to be copied (along with other relevant columns) to 
another worksheet in the same file.  Would this involve some sort of loop?  
Any help apreciated.

Thanks in advance,

Matt
0
7/29/2005 9:58:02 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
562 Views

Similar Articles

[PageSpeed] 7

there are several ways to do it.

if you want to copy a hundred cells for three columns every time something 
in row 3 is matched 
somehting like the following could be used.

sub try()
tar=1  ' target column in sheet 2
for c = 1 to 34 step 3  ' this will be the look up column in sheet 1
if sheets("Sheet1").Cells(c,3)=comparison value  then
       for sr=1 to 100  'Source row
       for sc=0 to 2  ' source column
            sheets("Sheet2").Cells(sr,tar+sc)=sheets("Sheet1").cells(sr,c+sc)
      next sc
      next sr
      tar = tar +3
end if
next c
end sub

I would make it more formal with dim statements etc. for actual use

"mattguerilla" wrote:

> I want to use code in a macro to search for criteria in a worksheet which 
> allows the matched data to be copied (along with other relevant columns) to 
> another worksheet in the same file.  Would this involve some sort of loop?  
> Any help apreciated.
> 
> Thanks in advance,
> 
> Matt
0
BJ (832)
7/29/2005 11:51:04 AM
"mattguerilla" <mattguerilla@discussions.microsoft.com> wrote in message
news:7726E1C0-CA5B-469B-90C0-0FB322461DA6@microsoft.com...
> I want to use code in a macro to search for criteria in a worksheet which
> allows the matched data to be copied (along with other relevant columns)
to
> another worksheet in the same file.  Would this involve some sort of loop?
> Any help apreciated.
>
> Thanks in advance,
>
> Matt

Here's one way:
First copy your data sheet for manipulation in case something goes wrong.
Add a sheet for each criteria then loop thru your data, moving each matching
row to the corresponding sheet.

'copy input data to a temp worksheet then switch to it for maniupation in
case anything goes wrong
Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "temp"

'add desitnation sheets for the matching data
    Sheets.Add.Name = "Match1"
    Sheets.Add.Name = "Match2"
    Sheets.Add.Name = "Match3"
    'copy title row to each of the new sheets
        For Each WS In Worksheets(Array("Match1", "Match2, "Match3"))
        WS.Range("A1:G1").Value = Sheets("Print").Range("A1:G1").Value
        Next

'any processing you do prior to seperating the matches needs completed here

'sort matching data to destination sheets
    Sheets("temp").Activate
    For Each cell In Range("G1:G" & Range("G65536").End(xlUp).Row)
    Select Case cell.Value
        Case "Match1":
        cell.EntireRow.Cut
Sheets("Match1").Range("A65536").End(xlUp).Offset(1, 0)

        Case "Match2":
        cell.EntireRow.Cut
Sheets("Match2").Range("A65536").End(xlUp).Offset(1, 0)

        Case "Match3":
        cell.EntireRow.Cut
Sheets("Match3").Range("A65536").End(xlUp).Offset(1, 0)
End Select
Next

'presuming you want to remove the temp worksheet once data is seperated,
without bothering the user
    Application.DisplayAlerts = False
    Sheets("temp").Delete
    Application.DisplayAlerts = True

'any followup processing goes here
end sub


0
Jefgorbach (42)
7/29/2005 2:25:00 PM
"Jef Gorbach" <Jefgorbach@aol.com> wrote in message
news:eYk8TlElFHA.3960@TK2MSFTNGP12.phx.gbl...
>
> "mattguerilla" <mattguerilla@discussions.microsoft.com> wrote in message
> news:7726E1C0-CA5B-469B-90C0-0FB322461DA6@microsoft.com...
> > I want to use code in a macro to search for criteria in a worksheet
which
> > allows the matched data to be copied (along with other relevant columns)
> to
> > another worksheet in the same file.  Would this involve some sort of
loop?
> > Any help apreciated.
> >
> > Thanks in advance,
> >
> > Matt
>
> Here's one way:
> First copy your data sheet for manipulation in case something goes wrong.
> Add a sheet for each criteria then loop thru your data, moving each
matching
> row to the corresponding sheet.
>
> 'copy input data to a temp worksheet then switch to it for maniupation in
> case anything goes wrong
> Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
> Sheets(Sheets.Count).Name = "temp"
>
> 'add desitnation sheets for the matching data
>     Sheets.Add.Name = "Match1"
>     Sheets.Add.Name = "Match2"
>     Sheets.Add.Name = "Match3"
>     'copy title row to each of the new sheets
>         For Each WS In Worksheets(Array("Match1", "Match2, "Match3"))
>         WS.Range("A1:G1").Value = Sheets("Print").Range("A1:G1").Value
>         Next
>
> 'any processing you do prior to seperating the matches needs completed
here
>
> 'sort matching data to destination sheets
>     Sheets("temp").Activate
>     For Each cell In Range("G1:G" & Range("G65536").End(xlUp).Row)
>     Select Case cell.Value
>         Case "Match1":
>         cell.EntireRow.Cut
> Sheets("Match1").Range("A65536").End(xlUp).Offset(1, 0)
>
>         Case "Match2":
>         cell.EntireRow.Cut
> Sheets("Match2").Range("A65536").End(xlUp).Offset(1, 0)
>
>         Case "Match3":
>         cell.EntireRow.Cut
> Sheets("Match3").Range("A65536").End(xlUp).Offset(1, 0)
> End Select
> Next
>
> 'presuming you want to remove the temp worksheet once data is seperated,
> without bothering the user
>     Application.DisplayAlerts = False
>     Sheets("temp").Delete
>     Application.DisplayAlerts = True
>
> 'any followup processing goes here
> end sub
>
>

Spotted a after sending: when copying the title row, sheets("print") should
have been sheets("temp")
Also during the matching, be sure to change column(g) to your longest data
column to catch everyone.


0
Jefgorbach (42)
7/29/2005 2:38:05 PM
Reply:

Similar Artilces:

Find Oldest date based on two criteria.
I am having an issue trying to figure out this issue.. I have tried a MIN, but it doesnt seem to work correctly.. I have three columns One with a State, one with a Code and one with a date. The Code can have say EA12 EA23 EA45 and FG12 FG34 FG56 What I need to have happen is that when State= NY and CODE Starts with EA find me the oldest date in the column. Example A B C NY EA12 1/1/2009 NY EA24 2/28/2009 CT EA12 3/2/2009 NY FG34 4/5/2009 ME FG45 5/1/2009 NY EA12 ...

How do I control where the new message window appears?
Whenever I create a new message the associated window appears with the maximise button off of my desktop. How do I change the default position of the new message window such that all of it is visible on my desktop? Resize and position it accordingly and hold CTRL when press the X to close the window. This should force storing the location and size of the window in the registry. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, R...

Create an index based on multiple criteria
Ok...here's what I'm trying to acheive: I am trying to create a form which will calculate who's signature is required based on several criteria. The three cases for the signatures are: District Sales Manager (DSM) only DSM & Managing Director (MD) DSM, MD, & Sales Director (SD) There are three factors used to determine the required signatures. So, there are three text boxes on my form. I have named those controls as follows: txtCM (Contribution Margin) [intCM] txtRV (Revenue) [intRV] txtCst (Cost) [intCST] And here are the different scenarios: 1. If intCM > 30 and i...

Opening excel file in new window
When an excel is already open in a window and I open a new excel file it opens in the same window. How do I make it open in a new window? You can't. Excel has two windows only; Excel and the VB editor. You may be able to open another instance of Excel though, with some Windows trick or by code. But that is spending a lot of power just for an extra window. HTH. best wishes Harald "Ravi" <Ravi@discussions.microsoft.com> skrev i melding news:B53F2A94-CE10-41F7-A3B0-D472317D527C@microsoft.com... > When an excel is already open in a window and I open a new excel file it ...

Problem with Table Column Not Expanding
I've got text in a table column that extends beyond the visible edge of the column. It appears that Word 2007 is not actually extending the table as it should but stops right at the page break. On the Table tab of Table properties, make sure text wrapping is set to None. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "David C. Holley" <David.C.Holley> wrote in message news:%23IRfeJbgKHA.5564@TK2MSFTNGP06.phx.gbl... > I've got text in a table column that extends beyond the visible edge...

SUMIF with Mutiple Ranges & Criteria
I'm doing a sales report and have a single sheet with all my call/resul metrics on it. I want to use SUMIF (I think ;) ) to calculate the cells in particular column if a cell in a specific column has a specific tex entry as well as a different text entry in another column (the tw columns are region and district). I know how do do a singl range/criteria in a SUMIF statement, but I don't know how to ad multiple SUMIF range/criterias. So basically here is what I am looking for: Sum a particular column G if column F equals "East Enterprise" an column V equals "CSI"...

Marketinglist export in dynamic worksheet
I have a CRM 3.0 environment with some strange behaviour when exporting all contacts from a marketinglist to a dynamic Excel worksheet. For example, the list has 50 members, but with the export I get all contacts from CRM. When using the static export, it works fine and it only exports the 50 members. I have tried another installation and here there are not problems when exporting to a dynamic Excel. Does anybody know if this is a (CRM) setting? -- CRM consultant ...

copy text to specific cell
In one cell I have a String And in another cell I have a reference to a cell. How do I put the String into the referenced cell? A Formula will return a value to the cell that holds the formula. You'd need some VBA to do what you want. dim myStrCell as range dim myAddrCell as range with activesheet set mystrcell = .range("a1") set myaddrcell = .range("b9") .range(myaddrcell.value).value = mystrcell.value end with --- This has no validation at all! If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritch...

How to embed and e-mail message into a new message with owa??
I have tried to insert and e-mail message into a new message using owa, but when I drag and drop the message to insert into the new message, it appears many codes, not the message itself. Is there any way to do this? ...

How do I create columns in a catalog merge?
How do I re-arrange the merge fields into columns instead of rows? (Top to bottom - 3 columns per page) Resize the "catalog merge area" until it says it will repeat 3 across. Resize the merge area vertically so it says Area will repeat 1 down. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "dcallaway" <dcallaway@discussions.microsoft.com> wrote in message news:56B87903-2B55-46F8-9A19-5089F9A1C2C4@microsoft.com... > How do I re-arrange the merge fields into columns instead of rows? (Top to > bottom...

Update from CRM 3.0 to new CRM 4.0
At the moment we are runnig CRM 3.0. Because of the new multi-language feature of CRM 4.0 we want tu upgrate/update to the new version. Does anybody has further information about the necessary steps? I suggest that you wait for the officially released version which will contain upgrade and installation guides. -- ----------------- Please note that the contents of my posts are my personal opinions and views and I am not a Microsoft staff member. ----------------- Feridun Kadir MCSE, MCT "Frank_Munich" wrote: > At the moment we are runnig CRM 3.0. Because of the new multi-...

Update Worksheet data to other worksheets Data
Hi All I have two Excel file. One Excel (A) create by User and other Excel (B) is shared version posted on company shared directory. I need update Worksheet B base on Worksheet A. In Worksheet A, the key is Col A + Col B + Col Cm matched to Worksheet B, the key is Col A + Col D + Col E Below Column need to update WS A to WS B. The column may be changed. After each Updated. Work Sheet A -> WorkSheet B I - > P J ->Q K ->R .... Any suggestion to using VBA to Update Excel B ? If Records not f...

Column Headings #5
I'm using Excel 2002 I've managed to get the column headings stuck on numbers, as well as the row headings, and I really want the column headings back as letters, so it's easier to differentiate. Can someone tell me where the option is, please? Tools>options>general uncheck R1C1 reference style -- Regards, Peo Sjoblom "Jonathan" <anonymous@discussions.microsoft.com> wrote in message news:041b01c3dd42$d1a90180$a001280a@phx.gbl... > I'm using Excel 2002 > > I've managed to get the column headings stuck on numbers, > as well as the row...

Excel fill handle copying does not copy formulas
My Excel has suddenly taken to ignoring the usual rules for the fill handle, and now will only copy the values of the first cell when dragged, instead of copying 'all' including formulas. I have explored all the possible settings, and they seem to be as they should be. I have opened a new sheet in the faulty workbook, and this exhibits the same aberrant behaviour. If I open a completely new workbook under the same Excel installation, it behaves normally, so the problem is confined to one particular workbook with five worksheets. This has behaved normally from day one until today...

Linking item between two worksheets
How do I link an item (letter, symbol) to another worksheet within the same workbook then be able to double-click on the item and it take me to that item. Similar to hyperlink within the same workbook. Test-drive this blast from the past: http://cjoint.com/?cypbTlFKDw Hyperlink_matching_record_in_another_sht voila? hit YES below -- Max Singapore --- "CD" wrote: > How do I link an item (letter, symbol) to another worksheet within the same > workbook then be able to double-click on the item and it take me to that > item. Similar to hyperlink within the sam...

Copy Blocks Of Data
Hi I'm working on an excel database at the moment and have come up agains a roadblock... I'm at the point where I have got Excel to sort all the data for m (HLOOKUP) into the correct categories and now I just want to record macro to copy it over in blocks to the database sheet. So... What I need it to do is take a whole block of data (each row is a entry) and place it into the database spreadsheet at the end of th last block. So each time I process (sort) a new block of data it paste it after the previous lot... Nic -- SenojN ----------------------------------------------------...

Matching DL Deposits with Invoice Payments
I have setup Money 2004 Small Business to download transactions from my bank. This is working fine. Most of the deposits are payments received for invoices. When I download the deposit record from the bank, it does not match the transaction with the existing transaction that was created when I run the "Receive payment for invoice" task in the Accounts Receivable window. Furthermore, when I try to manually match the downloaded transactions, the deposits created manually do not appear. What is wrong here? Is there something I'm missing or is there a problem with the program? ...

Xml Serialization, properties and 'new'
Hi all, I'm hoping that someone has seen this before. I wasn't able to find any solutions through my usual channels (I.E Google). I'm getting the following exception in certain situations when using the Xml Serializer to serialize a class that has changed the type of a property defined on its base class by using the 'new' keyword. Anyone seen this before? I've tried using the XmlElement attribute as the Exception suggests on the offending property to change the name, with no impact. Here's the exception : System.InvalidOperationException: There was an ...

Alter table Add column to merge publication
We are currently on SQL Server 2008 and I would like to add a column to a table that exists in our merge replication. Below is what I ran in our test environment Alter Table TableName Add ColumnNameTypeID uniqueidentifier Null The column was added, but the Default value or Binding was set to NewID() and the RowGuid property was set to Yes. I would like the Default value or Binding not set to anything and the RowGuid set to No. Is there a way to do this using the Alter Table command? The new column added is a FK to another table. I am thinking after the column is adde...

Problems with Columns
Attempting to change existing text to two columns but sections in wrong order when changed to columns. What could be causing this? ...

New disk message with outlook startup
There is no disk in the drive.Please insert a disk into > drive\Device\Harddisk3\DR6 I have suddenly started to receive this message. Pressing continue, the message appears three times and Outlook seems to operate normally. How can I get back to that blissful state when it just worked without this additional detritus? Try creating a new mail profile with the mail applet in the control panel. (don't copy the existing, create a new one and switch to it to see if error stops. If it does, then delete the old one as it just means you have a bad pointer to some file that e...

Excel opens worksheet multiple times #2
Thanks I'll give it a try -- murrayfa ----------------------------------------------------------------------- murrayfar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1578 View this thread: http://www.excelforum.com/showthread.php?threadid=27297 ...

How can I count the number of entries in a column?
I wish to construct Auction Lists for my Philatelic Society. At present I make the list up in an Acces DB and then transfer it to Excel (2003). At this point I add extra columns to cater for "Bidders #'s"; "Selling price"; "Price to Buyer";"Return to Vendor"; & "Return to Club", complete with relevant formulae. So far so good, I now wish to add, at the base of these columns, via a macro initially, a summary of data as entered. I also want to show, and use, in order to locate this data, the "TOTAL NUMBER OF LOTS". Si...

CONVERT AN EXCEL 2002 Worksheet
can anyone tell me if it's possible to convert a sheet into a .tiff file? Select the region of the worksheet required. Hold down SHIFT key, click menu item EDIT. Select item Copy Picture (use As Displayed or As Printed to suit need) Now you have a graphic of the worksheet. You can paste this on, for example, a PowerPoint slide To make a TIFF file you will need to paste it into a graphics program such as PaintShopPro as do as Save As to specify Tiff Best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in e-mail address "Liela" <lfuller@goodwin.com> w...

Need Formula to "Adjust" to New Column Assignment
Within the same workbook, I have 3 worksheets: Control, Upload, and Audit. Upload J2 = 500 Control J2 = 200 Currently, I have the following formula in the Audit worksheet: =IF((upload!J2-control!J2=0)," ",(upload!J2-control!J2) So, Audit J2 = 300. I then need to insert a column in all three worksheets between I and J. So the values that were formerly in Column J are now in Column K. Upload K2 value = 500 Control K2 value = 200 But the formula in Audit K2 still references J, so it's not showing 300. I need the formula that is now in Column K to be: =IF((upload!...