Filter and Copy selected data to a different worksheet

[LORDMARKS]

Hi All

I am looking for the cleanest way to pick selected rows of data and copy them to a different sheet where they will be used for dynamic graphs. The data on the first page will differ in size greatly, from 10 - 6000 rows, as it is an import from another system.

I have looked about online but most of the solutions I find seem way to complicated for what I really need. I was thinking to run a bit of code that looks line by line and if the value in column "A" is higher than a set value copy CELLS "A" to "G" to other sheet, then sort the data.

If anyone has any suggestions it would be much appreciated.


Kind Regards

 

[venkat1926]

main data in sheet1 is like this

Sheet1

*	A	B	C	D	E	F	G	H	I
hdng1	hdng2	hdng3	hdng4	hdng5	hdng6	hdng7	hdng8	hdng9
x	x
x	x
x	x
x	x
x	x
x	x
x	x

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]54[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]48[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]43[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]39[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]42[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]68[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]14[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

try this macro. modify to suit your data

Sub test()
Dim r As Range, setvalue As Double
setvalue = InputBox("type the setvalue for e.g. 34")
Worksheets("sheet2").Cells.Clear
With Worksheets("sheet1")
Set r = .Range("A1").CurrentRegion
r.AutoFilter field:=1, Criteria1:=">" & setvalue
r.Columns("A:G").SpecialCells(xlCellTypeVisible).Copy
With Worksheets("sheet2")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
.AutoFilterMode = False
End With
With Worksheets("sheet2")
Range("A2").CurrentRegion.Cells.Sort key1:=.Range("A1"), Header:=xlYes
End With
Application.CutCopyMode = False
MsgBox "macro done see sheet2"
End Sub