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

 

[propolis]

Thanks for update.

Did as suggested, but see results in attached image

Record from Actions Tab moved to On Hold Tab and back to Actions tab - that is fine, just the 'sorting' an problem, but will check in an hour, just on way home

Eddie

 

[GWteB]

According to the images you provide, you're switching columns.
Your query as of your post #1 reads:

What I am looking for is some VBA code that will always sort the first colum in my first four tabs

That's what the code does. So don't be surprised if the second column isn't in a sorted order:

 

[propolis]

Maybe I did not explain it correctly with the images I captured

Look at column A. First Column A shows row 2 as A001 and row 3 as A002 before the move. I do a move to Closed, then move the row back.

This is where column A row 2 shows A002 as the first row and A001 as the third row.

This should be auto filtered and show row 2 as A001 as the first row and A002 as the third row

Hope this helps

Eddie

 

[GWteB]

I did some tests using some sheets with the setup as per image below.

It works for me, although I should mention that in my test the data consists of contiguous rows.
If that's not the case then additional code is needed. I'll include it here just to be sure.
It's a replacement of my post #10 code and an additional function.

Public Sub SortSheetOnFirstColumn(ByVal argSht As Worksheet)
    Dim Rng As Range
    Set Rng = argSht.Range("A1", LastPopulatedCell(argSht.Cells))
    Rng.Sort Key1:=argSht.Range("A1"), Order1:=xlAscending, Header:=xlYes
End Sub


Public Function LastPopulatedCell(ByVal argRng As Range) As Range
    Dim x As Long, y As Long
    If Not argRng Is Nothing Then
        On Error Resume Next
        x = argRng.Find(What:="*", After:=argRng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
        y = argRng.Find(What:="*", After:=argRng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
        Set LastPopulatedCell = argRng.Parent.Cells(y, x)
        If Err.Number > 0 Then
            Set LastPopulatedCell = argRng.Cells(1)
            Err.Clear
        End If
    End If
End Function

 

[propolis]

Hi,

I am still having the same result. Could be me doing things wrong. Here is a link to the speadsheet on the server if you want to look: Spreasheet

I have removed tabs that have already sensitive data on them.

If its not feasable to do a download, that is fine. I can carry on as is. I have used up a lot of your time and appreciate all the help, but you also have other users to help

Eddie

 

[GWteB]

Just downloaded your file. The shoe pinches on two points:

1. The SortSheetOnFirstColumn procedure is never been invoked. See my post #4 to correct that.
2. Both the SortSheetOnFirstColumn procedure and LastPopulatedCell procedure (as provided in my post #14) are currently located in the ThisWorkbook module. That way the compiler cannot find the first one (**) since the ThisWorkbook module is a (special kind of) class module. That's why the compiler gave you a compile error. Both procedures were supposed to be pasted into a standard module (as per my instructions in post #10), just like most of the rest of the code. In the current situation the only procedure that remains in the ThisWorkbook module is the Workbook_SheetChange procedure.

** the second one is a dependency of the first one but since they're together in the same class module the compiler doesn't error on the second one. Nevertheless, put them both in a standard module.

 

[propolis]

Just downloaded your file. The shoe pinches on two points:

1. The SortSheetOnFirstColumn procedure is never been invoked. See my post #4 to correct that.
2. Both the SortSheetOnFirstColumn procedure and LastPopulatedCell procedure (as provided in my post #14) are currently located in the ThisWorkbook module. That way the compiler cannot find the first one (**) since the ThisWorkbook module is a (special kind of) class module. That's why the compiler gave you a compile error. Both procedures were supposed to be pasted into a standard module (as per my instructions in post #10), just like most of the rest of the code. In the current situation the only procedure that remains in the ThisWorkbook module is the Workbook_SheetChange procedure.

** the second one is a dependency of the first one but since they're together in the same class module the compiler doesn't error on the second one. Nevertheless, put them both in a standard module.

All is good now, done what you suggested and very happy now

Thank you very much

Eddie