Find certain values, then copy part of the line to another sheet

[KittyS]

Hi,
I tried to make something to find a certain value (from a userform) in a range, and then copy the line partially to another sheet. Then go forth to the next found item and do the same. But... I cannot seem to make this loop. Anybody can advise...?

With Sheets("Data input").Range("N5:N1100")
Dim FoundRange As Range

Worksheets("Data input").Activate

For X = 1 To 1000

Set FoundRange = Sheets("Data input").Cells.Find(what:=Planweeknr.Value, LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If FoundRange Is Nothing Then
End
Else
FoundRange.Select
ActiveCell.Select
ActiveCell.Offset(0, 2).Range("A1").Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select

ActiveCell.Activate
Selection.Copy

Sheets("Detail overzicht VAL").Select

Range("C5").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, -2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Select
ActiveWindow.SmallScroll Down:=6

End If

Next X

End With
Unload Me

 

[VoG]

Take a look at the Help for FindNext - there is a very good example.

 

[KittyS]

Hi,

I still can't manage with this. How do I put the FindNext with the scrolling down of the found items and COPY them?

Thx for your help!!

 

[VoG]

It is difficult to see what range you want to copy. But it isn't necessary to select anything, for example

FoundRange.Copy Destination:=Sheets("Detail overzicht VAL").Range("C" & Rows.Count).End(xlUp).Offset(1)

 

[KittyS]

It is difficult to see what range you want to copy. But it isn't necessary to select anything, for example

FoundRange.Copy Destination:=Sheets("Detail overzicht VAL").Range("C" & Rows.Count).End(xlUp).Offset(1)

Hi,

Sorry for being unclear.. I want to copy not only the found field, but in that row also some cells to right and left of the found field..
and then move down to the next found field and copy the cells of that row etc.

 

[VoG]

I can't work out what all that selecting is doing but you should be able to use Offset and Resize to specify the range to copy. For example

FoundRange.Offset(, -2).Resize(, 5).Copy Destination:=Sheets("Detail overzicht VAL").Range("C" & Rows.Count).End(xlUp).Offset(1)