Maybe this:

Sub CopyClosed()
Dim RngCol As Range
Dim i As Range
Dim Dest As Range
Sheets("Sheet1").Select
Set RngCol = Range("H1", Range("H" & Rows.Count).End(xlUp))
With Sheets("Sheet2")
Set Dest = .Range("A1")
End With
For Each i In RngCol
If i.Value = "Closed" Then
i.EntireRow.Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End Sub

HTH,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Don Guillett" wrote:

Try this idea. Modify to suit your sheets and range
Sub findtextcopyandhide()
With Worksheets("sheet15").Range("a1:a22")
Set c = .Find(What:="closed", After:=Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)

If Not c Is Nothing Then
firstAddress = c.Address
On Error Resume Next
Do
With Sheets("sheet6")
lr = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
Rows(c.Row).Copy .Rows(lr)
Rows(c.Row).Hidden = True
End With
Set c = .FindNext(c)
Loop While Not c Is Nothing _
And c.Address < firstAddress
End If
End With

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Vincent A. Somoredjo" <Vincent A.
wrote in message ...
Hallo,

Can you please provide some assistance?

I have the following code in a worksheet (Register), if the value in
Column
H is "Closed" I am hiding the row in the WorkSheet (Register). At the same
time I want to copy the hidden row to another sheet (Closed Issues).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lngRow As Long

Application.ScreenUpdating = False
For lngRow = 5 To lastRow
If Range("H" & lngRow).Value = "Closed" Then
Rows(lngRow + 0).Hidden = True
Else
Rows(lngRow + 0).Hidden = False
End If
Next
Application.ScreenUpdating = False
End Sub

Can this be done?

Thanks in advance for your support.