Selection by value of cell

[prija]

Hi,
I have one problem and I hope you will help me.

I would like to have a input box where I can insert a value (that some cells in one column contains). Depending on that value macro should make selection in active sheet,and that selection to copy in another sheet, automaticly.

Best regards.

 

[xlbo]

Sub CopySelections()
Dim lRow As Long, col As Integer, sval As Double
Application.ScreenUpdating = False
sval = Selection.Value
col = Selection.Column
lRow = ActiveSheet.Cells(65536, col).End(xlUp).Row
ActiveSheet.Range(Cells(1, col), Cells(lRow, col)).AutoFilter Field:=col, Criteria1:=sval
Range("A1:Z" & lRow).Copy Destination:=Sheets("Sheet2&quot.Range("A1&quot
ActiveSheet.AutoFilterMode = False
End Sub

this will filter for the value in the cell you select before running the code. Change "Sheet2" to the name of the sheet you want to export to
Change A:Z to be the range of data you want to be exported

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[prija]

I copy this procedure in my VB editor.
When I start macro, I got a Windows notice on row 6
ActiveSheet.Range(Cell(1,col),...

"400"
?!!!

I don't know what this meen?

Help me again!

 

[xlbo]

Works fine for me - tested it on some data
You must select a cell in your data that you want to filter by
ie if cell D10 has a value of 20 and you want to copy all the data which has a value of 20 into another sheet, you select D10 and then press play (the triangle on the VBE toolbar) and then choose the macro from the list
Also, you must have headers in row 1 and contiguous data below them
If it still doesn't work, please post back with the FULL error message

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes