Automatic filter

[propolis]

Hi,

I have received some code that works very well.

My spreadsheet has 6 Tabs.

From the first four tabs, Risk, Action, Issue and Dependency tabs I have a choice in a Status column to move the rows automatically to either of the next 2 tabs, Closed or On Hold.

When I go to these 2 tabs, I can select an option to move the row(s) back to where they came from using the Status column on thoes 2 tabs - this all works well

In my 4 tabs the first column is the ID column, eg in the risk tab you will see R001, next row R002, next row R003

In my 4 tabs the first column is the ID column, eg in the risk tab you will see A001, next row A002, next row A003

In my 4 tabs the first column is the ID column, eg in the risk tab you will see I001, next row I002, next row I003

In my 4 tabs the first column is the ID column, eg in the risk tab you will see D001, next row D002, next row D003

So after i sent R001 to Closed Tab, and move it back to the risk Tab, I see the following:

Now if I look at the first Tab - Risk and the ID column, I see the following : R002, next row R003 and then R001

What I am looking for is some VBA code that will always sort the first colum in my first four tabs ID column in order, so for Risk Tab after sending R001 back to it, I should see R001, R002, R003 etc

Hope this makes sense

Eddie

 

[GWteB]

This one-line procedure sorts the given worksheet (passed as an argument) as required.

Public Sub SortSheetOnFirstColumn(ByVal argSht As Worksheet)

    argSht.Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes

End Sub

Usage example:

SortSheetOnFirstColumn ActiveWorksheet

'or

SortSheetOnFirstColumn ThisWorkbook.Worksheets("Dependency")

 

[propolis]

This one-line procedure sorts the given worksheet (passed as an argument) as required.

Public Sub SortSheetOnFirstColumn(ByVal argSht As Worksheet)

    argSht.Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes

End Sub

Usage example:

SortSheetOnFirstColumn ActiveWorksheet

'or

SortSheetOnFirstColumn ThisWorkbook.Worksheets("Dependency")

Thank you for the quick update.

Where do I add this code in VB, not sure I am adding it in the correct place

Eddie

 

[GWteB]

Just paste the provided procedure in any standard module.
If you're still using the code from this post ...

Moving rows between tabs

Hi, In my attached spreadsheet the column status has a validation option of Open, On Hold and Closed When I select Open, it stays on that sheet. When I click On Hold, it moves the complete row to the On Hold tab. The same goes if I select Closed. Now when the row has been move to On Hold...

www.mrexcel.com

... then I would ammend the MoveRecord procedure like this:

Public Sub MoveRecord(ByVal argTarget As Range, ByVal argShtName As String)
    Dim DestSht As Worksheet
    With argTarget
        If WorksheetExists(.Parent.Parent, argShtName, DestSht) Then
            Application.EnableEvents = False
            .EntireRow.Copy DestSht.Range("A" & DestSht.Cells(DestSht.Rows.Count, "A").End(xlUp).Row + 1)
            .EntireRow.Delete
            SortSheetOnFirstColumn DestSht        ' <<<<
            Application.EnableEvents = True
        End If
    End With
End Sub

 

[propolis]

Just paste the provided procedure in any standard module.
If you're still using the code from this post ...

Moving rows between tabs

Hi, In my attached spreadsheet the column status has a validation option of Open, On Hold and Closed When I select Open, it stays on that sheet. When I click On Hold, it moves the complete row to the On Hold tab. The same goes if I select Closed. Now when the row has been move to On Hold...

www.mrexcel.com

... then I would ammend the MoveRecord procedure like this:

Public Sub MoveRecord(ByVal argTarget As Range, ByVal argShtName As String)
    Dim DestSht As Worksheet
    With argTarget
        If WorksheetExists(.Parent.Parent, argShtName, DestSht) Then
            Application.EnableEvents = False
            .EntireRow.Copy DestSht.Range("A" & DestSht.Cells(DestSht.Rows.Count, "A").End(xlUp).Row + 1)
            .EntireRow.Delete
            SortSheetOnFirstColumn DestSht        ' <<<<
            Application.EnableEvents = True
        End If
    End With
End Sub

Thank you very much, works well and again thank you for your help

Eddie

 

[GWteB]

You are welcome and thanks for letting me know

 

[propolis]

Hi,

Just spoken to the person who did the test for me, they used the wrong template I sent them to test.

In image one we select the 'Closed' option, and the image 2 shows the outcome.

The only change I made to the original code was: IDchar = Left(.Offset(0, -3).Value, 1) is chaning the (0, -3) it was (0, -2) and all still works fine - See 3rd image

When I rem out the line added all works fine

Any idea

Eddie

 

[GWteB]

You've only posted one image

 

[propolis]

Sorry, second one was to big, all 3 there now

Eddie

 

[GWteB]

You are showing me an image of a compile error. The compiler argues due to a missing procedure: "Sub or Function not defined"
The compiler highlights the name of the missing procedure, which is SortSheetOnFirstColumn on your image.
So the missing "Sub or Function" is the Sub in the code below. You need to paste this code in a standard module of the same project.
Don't use the code from my post #2, in that one a needed reference is missing.

Public Sub SortSheetOnFirstColumn(ByVal argSht As Worksheet)

    argSht.Range("A1").CurrentRegion.Sort Key1:=argSht.Range("A1"), Order1:=xlAscending, Header:=xlYes

End Sub