So confused why / how to automate my vba macros?

Question

Hello, new to VBA's and I'm honestly muddling through things.

I am trying to learn and thought I would make my own To Do list where the main sheet "ToDo" has a drop down option in column A (starting from A2) with options of "Q1" "W2" etc. When a drop down has been selected (e.g Q1) then I want the entire row to move to the same named worksheet (Q1).

I've used the following VBA in a module:

Sub MoveRowsToQ1()

Dim sourceSheet As Worksheet

Dim targetSheet As Worksheet

Dim lastRow As Long

Dim i As Long

' Set the source and target sheets

Set sourceSheet = ThisWorkbook.Worksheets("ToDo")

Set targetSheet = ThisWorkbook.Worksheets("Q1")

' Find the last row in the source sheet

lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row

' Loop through each row in the source sheet

For i = 2 To lastRow

' Check if cell in column A contains "Q1"

If sourceSheet.Cells(i, "A").Value = "Q1" Then

' Copy the entire row to the target sheet

sourceSheet.Rows(i).Copy Destination:=targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1)

' Delete the row from the source sheet

sourceSheet.Rows(i).Delete

' Decrement the loop counter as the rows are shifting up

i = i - 1

' Update the last row value

lastRow = lastRow - 1

End If

Next i

End Sub

I have then went into the macros table and selected "Run" and it works. But, it will not automatically move the rows when drop downs are selected, I need to manually run the macros each time.

Is there a way, and how would I do it, to make this automatic, so when the drop down is selected, the row moves straight away?

hansvogelaar · Answer

rebekah_mcmaster&nbsp;
It is possible to do that, but I would not recommend it. It would be very confusing if a row is whisked away the moment enters or selects Q1 in a cell in column A. It also makes it difficult to undo a mistake.
Instead, I would run the macro when (1) the workbook is opened, and (2) when the sheet with the data is deactivated (for example when you activate the Q1 sheet).
To do so:
(1) Right-click the sheet tab of the ToDo sheet and select 'View Code' from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Deactivate()
    Call MoveRowsToQ1
End Sub
(2) Still in the Visual Basic Editor, double-click ThisWorkbook in the project explorer pane on the left hand side.
Copy the following code into the ThisWorkbook module:
Private Sub Workbook_Open()
    Call MoveRowsToQ1
End Sub

Forum Discussion

So confused why / how to automate my vba macros?

Share

Resources