Move Row Automatically to Bottom of Active Table / Macro Help

[betachja]

Hi All,

I am trying to get my head around macros. Basically I have a table where I have drop down lists, once a dropdown is changed to 'Completed' or 'Stock Received' (Column B) I want the row to move to the bottom of the active table automatically on changing to the dropdown.

I've tried a few different codes in VBA that I have found, I have updated Trust Centre Settings to enable macros. Yet when I try to save the file it tells me "the following features cannot be saved in macro-free workbooks. Choose a macro-enabled file type in the file type list"....

So I am actually not sure if it is my code or a macro issue.

Any help would be greatly appreciated! I am new to this, yet learning on the go!

Many Thanks All

 

[My Aswer Is This]

Have you ever written a Macro before and saved your workbook as Macro Enable and then went back and ran the macro with sucess?

 

[betachja]

Have you ever written a Macro before and saved your workbook as Macro Enable and then went back and ran the macro with sucess?

Hi there,
Thankyou for reply.
No I have never written a macro before...
I did save it as a Macro Enabled file and changed the dropdown to 'completed' but it didn't move the row so I assume the macro didn't work.
I am new to it all

 

[betachja]

Have you ever written a Macro before and saved your workbook as Macro Enable and then went back and ran the macro with sucess?

I tried this macro code that I found on here:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, c As Range, mvrng As Range
Dim lr As Long

Set rng = Intersect(Target, Range("B4:B" & Rows.Count))
If Not rng Is Nothing Then
For Each c In rng
If LCase(c.Value) = LCase("completed") Then
If mvrng Is Nothing Then Set mvrng = Range("A" & c.Row & ":I" & c.Row) Else Set mvrng = Union(mvrng, Range("A" & c.Row & ":I" & c.Row))
End If
Next
If Not mvrng Is Nothing Then
Application.EnableEvents = False
mvrng.Interior.ColorIndex = 15
mvrng.Copy Range("A" & Rows.Count).End(3)(2)
mvrng.Delete Shift:=xlUp
Application.EnableEvents = True
End If
End If
End Sub

 

[My Aswer Is This]

I always think users new to Vba should start off simple and then work at more complicates scripts.

So, write a simple script like this and see if it works

Range("A1").value="Alpha"
Put this script in a Button and when you press the button the script should run

And then to do what you want explain in words what your trying to do.
Do not post a script that does not work and expect me to understand what your attempting to do.

 

[betachja]

My apologies, I thought I was here for assistance as I explained I am new to this. I also thought I explained in the first comment what what I was trying to do.
My error..
Thanks for the replies

 

[My Aswer Is This]

My apologies, I thought I was here for assistance as I explained I am new to this. I also thought I explained in the first comment what what I was trying to do.
My error..
Thanks for the replies

First I'm trying to see if your workbook is actually Macro enabled.
So if my simple script works you know the workbook is Macro Enable.
And then we move to step 2

 

[betachja]

Thankyou, greatly appreciated.
It is macro enabled, I have a script working on it now.
So it works for what is already marked as 'completed' (Column B) in the table, I am just trying to figure out how to get it to work when changing a a dropdown status to completed (eg from In Progress to Completed), so that going forward it automatically moves the 'completed' row to the bottom.
I must be missing something in the script for future changes...

The script I have working is:

Sub MoveToEnd()
Dim xRg As Range
Dim xTxt As String
Dim xCell As Range
Dim xEndRow As Long
Dim I As Long
On Error Resume Next
If ActiveWindow.RangeSelection.Count > 1 Then
xTxt = ActiveWindow.RangeSelection.AddressLocal
Else
xTxt = ActiveSheet.UsedRange.AddressLocal
End If
lOne:
Set xRg = Application.InputBox("Select range:", "Kutools for Excel", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
If xRg.Columns.Count > 1 Or xRg.Areas.Count > 1 Then
MsgBox " Multiple ranges or columns have been selected ", vbInformation, "Kutools for Excel"
GoTo lOne
End If
xEndRow = xRg.Rows.Count + xRg.Row
Application.ScreenUpdating = False
For I = xRg.Rows.Count To 1 Step -1
If xRg.Cells(I) = "Completed" Then
xRg.Cells(I).EntireRow.Cut
Rows(xEndRow).Insert Shift:=xlDown
End If
Next
Application.ScreenUpdating = True
End Sub

Thanks again

 

[My Aswer Is This]

Thankyou, greatly appreciated.
It is macro enabled, I have a script working on it now.
So it works for what is already marked as 'completed' (Column B) in the table, I am just trying to figure out how to get it to work when changing a a dropdown status to completed (eg from In Progress to Completed), so that going forward it automatically moves the 'completed' row to the bottom.
I must be missing something in the script for future changes...

The script I have working is:

Sub MoveToEnd()
Dim xRg As Range
Dim xTxt As String
Dim xCell As Range
Dim xEndRow As Long
Dim I As Long
On Error Resume Next
If ActiveWindow.RangeSelection.Count > 1 Then
xTxt = ActiveWindow.RangeSelection.AddressLocal
Else
xTxt = ActiveSheet.UsedRange.AddressLocal
End If
lOne:
Set xRg = Application.InputBox("Select range:", "Kutools for Excel", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
If xRg.Columns.Count > 1 Or xRg.Areas.Count > 1 Then
MsgBox " Multiple ranges or columns have been selected ", vbInformation, "Kutools for Excel"
GoTo lOne
End If
xEndRow = xRg.Rows.Count + xRg.Row
Application.ScreenUpdating = False
For I = xRg.Rows.Count To 1 Step -1
If xRg.Cells(I) = "Completed" Then
xRg.Cells(I).EntireRow.Cut
Rows(xEndRow).Insert Shift:=xlDown
End If
Next
Application.ScreenUpdating = True
End Sub

Thanks again

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[betachja]

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

I'm watching it too lol, but I think it has something to do with Application.EnableEvents although not sure. Hopefully someone can shed some light on it for us and we've both learnt.
Thanks for your replies, greatly appreciated