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.

Try something like this. Change the new sheet name as required.

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

Application.ScreenUpdating = False
Application.EnableEvents = False
OldSht = Target.Parent
Set NewSht = Sheets("Sheet2")
NewRow = 1
With Parent
lastRow = .Range("H" & Rows.Count).End(xlUp).Row

For lngRow = 5 To lastRow
If .Range("H" & lngRow).Value = "Closed" Then
.Rows(lngRow).Copy _
Destination:=NewSht.Rows(NewRow)
NewRow = NewRow + 1
.Rows(lngRow).Hidden = True
Else
.Rows(lngRow).Hidden = False
End If
Next lngRow
End With
Application.EnableEvents = False
Application.ScreenUpdating = False
End Sub


"Vincent A. Somoredjo" wrote:

> 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.

I don't think you want to use the SelectionChange event; rather, I would
think you would want to use the Change event and monitor Column H within it,
copying/hiding any row where you make an entry of "Closed" in Column H.
Something like this maybe....

Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range
Dim LastRow As Long
For Each C In Target
If C.Column = 8 Then
If LCase(C.Value) = "closed" Then
With Worksheets("Closed Issues")
LastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
C.EntireRow.Copy .Cells(LastRow + 1, "A")
End With
C.EntireRow.Hidden = True
End If
End If
Next
End Sub

--
Rick (MVP - Excel)


"Vincent A. Somoredjo" <Vincent A. (E-Mail Removed)>
wrote in message news:11A11D6A-BB66-4DB8-A663-(E-Mail Removed)...
> 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.

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
(E-Mail Removed)
"Vincent A. Somoredjo" <Vincent A. (E-Mail Removed)>
wrote in message news:11A11D6A-BB66-4DB8-A663-(E-Mail Removed)...
> 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.

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
> (E-Mail Removed)
> "Vincent A. Somoredjo" <Vincent A. (E-Mail Removed)>
> wrote in message news:11A11D6A-BB66-4DB8-A663-(E-Mail Removed)...
> > 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.
>
>