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

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

"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

"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.