Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
"mattguerilla" wrote in message ... 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 |
#4
|
|||
|
|||
"Jef Gorbach" wrote in message ... "mattguerilla" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum Count of Single Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
Copying multiple columns not adjacent | Excel Discussion (Misc queries) | |||
SUMIF in multiple columns based on other criteria in Excel? | Excel Discussion (Misc queries) | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions |