copying code

[scottappleford]

Hi

if you can help i would be really grateful

I have worksheet with 6 columns for each row of information. If I type say the number 3 in the status column, then the row of information should transfer to a separate 'closed account' worksheet, and automatically filter by date. There are formulas in the cells however i only want the values to be transferred?

much appreciated

scott

 

[Brianwarnock]

I don't understand the bit about automatically filtering by date but in the worksheet change event code such as below will do the copying, the status colmn is assumed to be F ie the 6th

Brian

Application.CutCopyMode = False

If Target.Column = 6 And Target.Value = 3 Then
Target.EntireRow.Copy
    With Worksheets("closed")
    .Rows("3:3").Insert Shift:=xlDown
    .Range("A3").PasteSpecial
    End With
End If

Application.CutCopyMode = True

 

[scottappleford]

Hi

thanks the help

one of the cells in the row is a date the acct closed and i just wanted the account closed sheet to be sorted by date - if that makes sense?

thanks again

 

[scottappleford]

Hi again

i have selected view code, selected worksheet from the drop down but what event do i need from the right drop down.

thanks

scott

 

[Brianwarnock]

Change

Brian

 

[Brianwarnock]

Hi

thanks the help

one of the cells in the row is a date the acct closed and i just wanted the account closed sheet to be sorted by date - if that makes sense?

thanks again

Hi missed this last night as I was taking an urgent call.
You may have solved this by now but this is the approach I would take given the Sort.

Brian

Private Sub Worksheet_Change(ByVal Target As Range)
' assumes Date in col 1 and Status in col 6 headings in Row 1
' 2 sheets named open and closed
' This is in the Change event of sheet open

Dim trow As Integer
Dim lastrow As Integer

If Target.Column <> 6 Then Exit Sub

Application.CutCopyMode = False
lastrow = Worksheets("closed").UsedRange.Rows.Count
trow = Target.Row

If Target.Value = 3 Then
Target.EntireRow.Copy
Worksheets("closed").Cells(lastrow + 1, 1).PasteSpecial
 Worksheets("closed").Range("A1").Sort _
        Key1:=Worksheets("closed").Columns("A"), _
        Header:=xlYes

Worksheets("open").Rows(trow).Delete   'remove this and ref to trow if you do not wish to delete
End If

Application.CutCopyMode = True

End Sub