Hi
I am looking to view the data in column k2 to the end of the available data,
and copy the rows that column that house the 3 largest values and 3 smallest
values, before copying the six selected rows into the next worksheet
(worksheet 3).
Any idea of how this can be done?

Try the below. Adjust the sheet name to suit...

Sub ReFormat2()
Dim c As Range, rng As Range, lngRow As Long
Set rng = Range("K2:K" & Cells(Cells.Rows.Count, "K").End(xlUp).Row)
For Each c In rng
If WorksheetFunction.Large(rng, 1) = c.Value Or _
WorksheetFunction.Large(rng, 2) = c.Value Or _
WorksheetFunction.Large(rng, 3) = c.Value Or _
WorksheetFunction.Small(rng, 1) = c.Value Or _
WorksheetFunction.Small(rng, 2) = c.Value Or _
WorksheetFunction.Small(rng, 3) = c.Value Then _
lngRow = lngRow + 1: Rows(c.Row).Copy Sheets("Sheet3").Rows(lngRow)
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Withnails" wrote:

Hi
I am looking to view the data in column k2 to the end of the available data,
and copy the rows that column that house the 3 largest values and 3 smallest
values, before copying the six selected rows into the next worksheet
(worksheet 3).
Any idea of how this can be done?

Why not simply use a formula and then copy values?
Assuming your values are in column A you can use this in column B
=LARGE(INDEX(A$1:A$100,LARGE(ROW(A$1:A$100)*ISNUMB ER(A$1:A$100),100)):A$100,ROWS(B$1:B1)),
after pasting it to the formula bar it must be comitted using
Ctrl+Shift+Enter as its an array formula, if you have done it correctly
it will show { at either end of the formula, after you have done this
copy down 3 cells, in the next cell (B4) do the same with this formula
=SMALL(INDEX(A$1:A$100,SMALL(ROW(A$1:A$100)*ISNUMB ER(A$1:A$100),1)):A$100,ROWS(B$4:B4))
you should now have six figures 3 top and 3 bottom, simply copy and
paste the values to another sheet!

Withnails;508054 Wrote:
Hi
I am looking to view the data in column k2 to the end of the available
data,
and copy the rows that column that house the 3 largest values and 3
smallest
values, before copying the six selected rows into the next worksheet
(worksheet 3).
Any idea of how this can be done?


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=139680

SORT the column, then take K2:J4 and the last three rows

MyTable.Sort Range("K1")


if you need to keep the order, then add a column for the original row number
, then sort again off this.




"Withnails" wrote:

Hi
I am looking to view the data in column k2 to the end of the available data,
and copy the rows that column that house the 3 largest values and 3 smallest
values, before copying the six selected rows into the next worksheet
(worksheet 3).
Any idea of how this can be done?

this works well, but grabs data from column K on sheet1, how can i adjust it
to grab info from sheet2?

"Jacob Skaria" wrote:

Try the below. Adjust the sheet name to suit...

Sub ReFormat2()
Dim c As Range, rng As Range, lngRow As Long
Set rng = Range("K2:K" & Cells(Cells.Rows.Count, "K").End(xlUp).Row)
For Each c In rng
If WorksheetFunction.Large(rng, 1) = c.Value Or _
WorksheetFunction.Large(rng, 2) = c.Value Or _
WorksheetFunction.Large(rng, 3) = c.Value Or _
WorksheetFunction.Small(rng, 1) = c.Value Or _
WorksheetFunction.Small(rng, 2) = c.Value Or _
WorksheetFunction.Small(rng, 3) = c.Value Then _
lngRow = lngRow + 1: Rows(c.Row).Copy Sheets("Sheet3").Rows(lngRow)
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Withnails" wrote:

Hi
I am looking to view the data in column k2 to the end of the available data,
and copy the rows that column that house the 3 largest values and 3 smallest
values, before copying the six selected rows into the next worksheet
(worksheet 3).
Any idea of how this can be done?

Try the below code...

Sub ReFormat2()
Dim c As Range, rng As Range, lngRow As Long, Dim ws as Worksheet
Set ws = Worksheets("Sheet2")
Set rng = ws.Range("K2:K" & ws.Cells(Cells.Rows.Count, "K").End(xlUp).Row)
For Each c In rng
If WorksheetFunction.Large(rng, 1) = c.Value Or _
WorksheetFunction.Large(rng, 2) = c.Value Or _
WorksheetFunction.Large(rng, 3) = c.Value Or _
WorksheetFunction.Small(rng, 1) = c.Value Or _
WorksheetFunction.Small(rng, 2) = c.Value Or _
WorksheetFunction.Small(rng, 3) = c.Value Then _
lngRow = lngRow + 1: ws.Rows(c.Row).Copy Sheets("Sheet3").Rows(lngRow)
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Withnails" wrote:

this works well, but grabs data from column K on sheet1, how can i adjust it
to grab info from sheet2?

"Jacob Skaria" wrote:

Try the below. Adjust the sheet name to suit...

Sub ReFormat2()
Dim c As Range, rng As Range, lngRow As Long
Set rng = Range("K2:K" & Cells(Cells.Rows.Count, "K").End(xlUp).Row)
For Each c In rng
If WorksheetFunction.Large(rng, 1) = c.Value Or _
WorksheetFunction.Large(rng, 2) = c.Value Or _
WorksheetFunction.Large(rng, 3) = c.Value Or _
WorksheetFunction.Small(rng, 1) = c.Value Or _
WorksheetFunction.Small(rng, 2) = c.Value Or _
WorksheetFunction.Small(rng, 3) = c.Value Then _
lngRow = lngRow + 1: Rows(c.Row).Copy Sheets("Sheet3").Rows(lngRow)
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Withnails" wrote:

Hi
I am looking to view the data in column k2 to the end of the available data,
and copy the rows that column that house the 3 largest values and 3 smallest
values, before copying the six selected rows into the next worksheet
(worksheet 3).
Any idea of how this can be done?