VBA to Move Rows Based on Status in Excel

[bgwhalen]

Hello everyone,

I'm new to VBA programming in Excel and I'm trying to automate a process where I move a row from my "Training" sheet to a "Historical" sheet when the value in Column G (Status) is set to "Inactive." However, I keep encountering an error with my code, and I'm not sure how to fix it.

Here's a brief overview of what I have done so far:

1. **Trigger**: The code should run when the status in Column G is changed to "Inactive."
2. **Action**: Move the entire row from the "Training" sheet to the "Historical" sheet.
3. **Action**: The move button when clicked should move the row that has “Inactive” selected in Column G.
3. **Error**: I’m encountering an error when I run my code, but I’m unsure what the issue is.


I have attached pictures of my code and the relevant parts of my Excel file for reference.

Questions:
- What could be causing the error in my code?
- Is there a better way to structure this code?
- How can I ensure this runs automatically when the status is changed?

I appreciate any help or guidance you can provide!

Thank you!

 

[bgwhalen]

Here is what the sheet looks like with the Move row button

 

[My Aswer Is This]

If you want the script to run when you enter "Inactive" into column "G"
You do not need a Button.

The below script will run when you enter the value "Inactive" into column G

Right click on the Sheet Tab named ""Training" Choose View and enter this script

Then any time you enter "Inactive" into any cell in column G that row will be copied to sheet named "Historical" and deleted from sheet named "Training"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim Lastrow As Long
Lastrow = Sheets("Historical").Cells(Rows.Count, "G").End(xlUp).Row + 1
If Target.Value = "Inactive" Then
Rows(Target.Row).Copy Sheets("Historical").Cells(Lastrow, 1)
Rows(Target.Row).Delete

End If
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[Alex Blakenburg]

The solutions provided by @My Aswer Is This, is the way to go but in terms of

- What could be causing the error in my code?

The error you are getting indicates that it can't find the sheet "Training".
Looking at you image the little green line under the Training sheet name looks to goes past the end of the "g" and indicates you have a space after the word Training.