auto copying between sheets

[megaman666]

Hi, no idea if this is possible..

I have 2 worksheets, 'open' and 'closed'. There is a column on the 'open' sheet called status, which can be either open, closed, or on hold.

Is there any way that I could auto-copy the row onto the closed tab once the status is swicthed to closed? (it would also need to be deleted from that tab if the status is changed back to open or on hold???

probably a bit too complicated..

 

[megaman666]

any ideas at all how to achieve something even similar would be really appreciated

 

[farmerscott]

A change event will do the job.

What column does "Status" reside in?

FarmerScott

 

[megaman666]

Its column B on the OPEN tab- there is an auto filter that will automatically hide rows which have the status switched to CLOSED- however I would like these rows upon being hidden to be duplicated onto the CLOSED tab- if the status is later changed to something else, to remove it (delete the row) from the CLOSED tab

 

[farmerscott]

Valery,

can you post your full code. (Highlight your code and hit the # sign so it is wrapped in code tags.)

thanks.

 

[megaman666]

Private Sub Worksheet_Change(ByVal Target As Range)
If Me.FilterMode = True Then
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
With ActiveWorkbook
.CustomViews.Add ViewName:="Mine", RowColSettings:=True
Me.AutoFilterMode = False
.CustomViews("Mine").Show
.CustomViews("Mine").Delete
End With

With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End If
End Sub


thats all i have at the moment for the auto refresh, ive no idea how to tackly the main bit

 

[farmerscott]

Put this code in your Sheet ("Open") module..

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lr As Long
Dim lr2 As Long
Dim rng As Range

lr = Worksheets("Open").Cells(Rows.Count, "B").End(xlUp).Row
 
If Target.Column <> 2 Then
Exit Sub
Else
Set rng = Sheets("Open").Range("A2:Q" & lr) 'assumes a header row and change "Q" to your last col.
    rng.EntireRow.Hidden = False


For x = lr To 2 Step -1 'assumes a header row
    
If Cells(x, 2).Value = "Closed" Then
    
    lr2 = Worksheets("Closed").Cells(Rows.Count, "B").End(xlUp).Row
    Rows(x).EntireRow.Copy Worksheets("Closed").Range("A" & lr2 + 1)
    Rows(x).EntireRow.Hidden = True
    
    End If
  Next x
 End If
End Sub

put this code in your Sheet("Closed") module

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lr As Long
Dim lr2 As Long
Dim rng As Range

lr = Worksheets("Closed").Cells(Rows.Count, "B").End(xlUp).Row
 
If Target.Column <> 2 Then
Exit Sub
Else
Set rng = Sheets("Closed").Range("A2:Q" & lr) 'assumes a header row
    rng.EntireRow.Hidden = False
For x = lr To 2 Step -1 'assumes a header row
    If Cells(x, 2).Value <> "Closed" Then
    
    lr2 = Worksheets("Open").Cells(Rows.Count, "B").End(xlUp).Row
    Rows(x).EntireRow.Copy Worksheets("Open").Range("A" & lr2 + 1)
    Rows(x).EntireRow.Delete
    
    End If
  Next x
 End If
End Sub

Let me know if that is firing OK when you make the changes.

FarmerScott

 

[megaman666]

hmm, I get an ambiguous name error??

 

[farmerscott]

You have two subs named the same.