Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Entire Row based on Cell Value
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Entire Row based on Cell Value
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Entire Row based on Cell Value
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. 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Entire Row based on Cell Value
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Entire Row based on Cell Value
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy an entire row based on value in one cell | Excel Programming | |||
Help: auto-copy entire rows from 1 sheet (based on cell criteria) to another sheet. | Excel Programming | |||
Copy entire row to another sheet based on a criteria | Excel Discussion (Misc queries) | |||
copy and insert entire row based on integer in column A | Excel Programming | |||
Formatting an entire row based on one cell | Excel Worksheet Functions |