Automatic filter

propolis

New Member
Joined
Mar 22, 2019
Messages
32
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,596
Office Version
  1. 2013
Platform
  1. Windows
This one-line procedure sorts the given worksheet (passed as an argument) as required.

VBA Code:
Public Sub SortSheetOnFirstColumn(ByVal argSht As Worksheet)

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

End Sub

Usage example:
VBA Code:
SortSheetOnFirstColumn ActiveWorksheet

'or

SortSheetOnFirstColumn ThisWorkbook.Worksheets("Dependency")
 

propolis

New Member
Joined
Mar 22, 2019
Messages
32
This one-line procedure sorts the given worksheet (passed as an argument) as required.

VBA Code:
Public Sub SortSheetOnFirstColumn(ByVal argSht As Worksheet)

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

End Sub

Usage example:
VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,596
Office Version
  1. 2013
Platform
  1. Windows
Just paste the provided procedure in any standard module.
If you're still using the code from this post ...


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

Rich (BB code):
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

New Member
Joined
Mar 22, 2019
Messages
32

ADVERTISEMENT

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


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

Rich (BB code):
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,596
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for letting me know (y)
 

propolis

New Member
Joined
Mar 22, 2019
Messages
32

ADVERTISEMENT

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
 

Attachments

  • Screenshot 2021-11-24 at 16.39.00.png
    Screenshot 2021-11-24 at 16.39.00.png
    71.7 KB · Views: 5
  • Screenshot 2021-11-24 at 16.39.jpg
    Screenshot 2021-11-24 at 16.39.jpg
    161.8 KB · Views: 4
  • Screenshot 2021-11-24 at 16.43.55.png
    Screenshot 2021-11-24 at 16.43.55.png
    129.1 KB · Views: 4
Last edited:

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,596
Office Version
  1. 2013
Platform
  1. Windows
You've only posted one image :unsure:
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,596
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
Public Sub SortSheetOnFirstColumn(ByVal argSht As Worksheet)

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

End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,957
Messages
5,767,308
Members
425,403
Latest member
MellieD

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top