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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> "mattguerilla" <[email protected]> wrote in message
> news:[email protected]...
> > 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.