How can I have macro running without pressing run each time ?

[GabyS2023]

I have the following macro and it works perfectly, however I have to press run each time, how can I have this run when I make changes in Column "AA"?

 

[Joe4]

Welcome to the Board!

As long as you are making the changes in column AA manually, and it is not the result of a formula, you can use a "Worksheet_Change" event procedure, which is VBA code that is run automatically when data is manually updated.

The only question is, when a cell in column AA is updated, do you really want/need the code to run against ALL rows on the sheet, or just the row that gets updated?

Also, please post your code using Code Tags so we can easily copy/paste it (we cannot do anything with a picture of your code).
See here: How to Post Your VBA Code

 

[GabyS2023]

Welcome to the Board!

As long as you are making the changes in column AA manually, and it is not the result of a formula, you can use a "Worksheet_Change" event procedure, which is VBA code that is run automatically when data is manually updated.

The only question is, when a cell in column AA is updated, do you really want/need the code to run against ALL rows on the sheet, or just the row that gets updated?

Also, please post your code using Code Tags so we can easily copy/paste it (we cannot do anything with a picture of your code).
See here: How to Post Your VBA Code

Hi Joe! thanks for the reply, answering to your question, I only need the cells that get updated in column AA:

Sub MoveRowsToTireCases()
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("Naknek Salmon")
Set targetSheet = ThisWorkbook.Worksheets("Cancelled_No Show")

' Find the last row in the source sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "AA").End(xlUp).Row

' Loop through each row in the source sheet
For i = 2 To lastRow
' Check if cell in column AA contains "Cancelled"
If sourceSheet.Cells(i, "AA").Value = "Cancelled" 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

 

[Joe4]

Go to your "Naknek Salmon" sheet and right-click on the Sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code int eh VB Editor box that pops up (the code MUST go in this particular sheet module in order to work automatically):

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim targetSheet As Worksheet
    Dim rng As Range
    Dim cell As Range

'   Set the source and target sheets
    Set targetSheet = ThisWorkbook.Worksheets("Cancelled_No Show")

'   Check for update to column AA
    Set rng = Intersect(Target, Columns("AA:AA"))

'   Exit if no update to column AA
    If rng Is Nothing Then Exit Sub

'   Loop through update cells
    For Each cell In rng
'       See of update after row 1 and is "Cancelled"
        If (cell.Row > 1) And (cell.Value = "Cancelled") Then
'           Copy the entire row to the target sheet
            Rows(cell.Row).Copy Destination:=targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1)
'           Delete the row from the source sheet
            Application.EnableEvents = False
            Rows(cell.Row).Delete
            Application.EnableEvents = True
        End If
    Next cell
    
End Sub

Then, as you manually put "Cancelled" in any cell in column AA, it should automatically move the row over to the other sheet.

 

[GabyS2023]

Go to your "Naknek Salmon" sheet and right-click on the Sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code int eh VB Editor box that pops up (the code MUST go in this particular sheet module in order to work automatically):

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim targetSheet As Worksheet
    Dim rng As Range
    Dim cell As Range

'   Set the source and target sheets
    Set targetSheet = ThisWorkbook.Worksheets("Cancelled_No Show")

'   Check for update to column AA
    Set rng = Intersect(Target, Columns("AA:AA"))

'   Exit if no update to column AA
    If rng Is Nothing Then Exit Sub

'   Loop through update cells
    For Each cell In rng
'       See of update after row 1 and is "Cancelled"
        If (cell.Row > 1) And (cell.Value = "Cancelled") Then
'           Copy the entire row to the target sheet
            Rows(cell.Row).Copy Destination:=targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1)
'           Delete the row from the source sheet
            Application.EnableEvents = False
            Rows(cell.Row).Delete
            Application.EnableEvents = True
        End If
    Next cell
   
End Sub

Then, as you manually put "Cancelled" in any cell in column AA, it should automatically move the row over to the other sheet.This

 

[GabyS2023]

Go to your "Naknek Salmon" sheet and right-click on the Sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code int eh VB Editor box that pops up (the code MUST go in this particular sheet module in order to work automatically):

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim targetSheet As Worksheet
    Dim rng As Range
    Dim cell As Range

'   Set the source and target sheets
    Set targetSheet = ThisWorkbook.Worksheets("Cancelled_No Show")

'   Check for update to column AA
    Set rng = Intersect(Target, Columns("AA:AA"))

'   Exit if no update to column AA
    If rng Is Nothing Then Exit Sub

'   Loop through update cells
    For Each cell In rng
'       See of update after row 1 and is "Cancelled"
        If (cell.Row > 1) And (cell.Value = "Cancelled") Then
'           Copy the entire row to the target sheet
            Rows(cell.Row).Copy Destination:=targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1)
'           Delete the row from the source sheet
            Application.EnableEvents = False
            Rows(cell.Row).Delete
            Application.EnableEvents = True
        End If
    Next cell
   
End Sub

Then, as you manually put "Cancelled" in any cell in column AA, it should automatically move the row over to the other sheet.

this worked! thank you so much

 

[Joe4]

You are welcome.
Glad I was able to help!

Please note: when marking posts as the solution, please mark the original reply containing the solution, not your own post acknowledging that some other post is the solution.
I have updated this for you on this thread.