VBA code to move rows to certain sheets based on various values in last cell

[DeFox98]

First off, I am very new to VBA, so please consider that.

What I am trying to do is create a code that moves a row to a specific sheet based on the value in the last cell of that row. I work at a tier 1 automotive supplier, so id like to be able to enter a part #, some specs, and the machine that it is run on into a row then have a macro cut and paste that row's data into a sheet titled with that machines name with the top row of each sheet being the titles for the columns.
There are sheets titled Sheet 1, AMP 30, AMP 50, AMP 71, AMP 73, AMP 74, and AMP 76.

ex: columns A-G will contain the part # in A and end with the machine number in G (30,50,71,73,74,76)
when any of those numbers are put in column G, it will move that row to the sheet titled accordingly. (30 to AMP 30, etc)
then after, where the data just was will be blank

Basically i want to be able to enter an unlimited number of parts into sheet 1, hit the macro button located somewhere on sheet1, and have it sort my data into its appropriate sheets.

I have seen some similar codes on this forum while i was googling, however I dont understand this well enough to modify a similar code to suit my needs.

Hopefully my idea makes sense to you guys, and hopefully my grammar is well enough to understand. Engineers are much better with numbers than with words, lol.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in column G and exit the cell. That row will be automatically copied to the appropriate sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub
    Target.EntireRow.Copy Sheets("AMP " & Target.Value).Cells(Sheets("AMP " & Target.Value).Rows.Count, "A").End(xlUp).Offset(1, 0)
End Sub

 

[DeFox98]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in column G and exit the cell. That row will be automatically copied to the appropriate sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub
    Target.EntireRow.Copy Sheets("AMP " & Target.Value).Cells(Sheets("AMP " & Target.Value).Rows.Count, "A").End(xlUp).Offset(1, 0)
End Sub

WOW!! That works great. thank you so much. Now is there a way to add a button? Its not entirely necessary, but I like the idea.

 

[DeFox98]

Basically, I want to have the ability to run the macro via a button instead of it automatically running.

 

[mumps]

Place this macro in a standard module and assign it to your button. Enter a value in column G and click the button.

Sub CopyRow()
    ActiveCell.EntireRow.Copy Sheets("AMP " & ActiveCell.Value).Cells(Sheets("AMP " & ActiveCell.Value).Rows.Count, "A").End(xlUp).Offset(1, 0)
End Sub

 

[DeFox98]

Place this macro in a standard module and assign it to your button. Enter a value in column G and click the button.

Sub CopyRow()
    ActiveCell.EntireRow.Copy Sheets("AMP " & ActiveCell.Value).Cells(Sheets("AMP " & ActiveCell.Value).Rows.Count, "A").End(xlUp).Offset(1, 0)
End Sub

It comes up with an error saying "Run-time error '9': Subscript out of range"

 

[DeFox98]

I figured out the issue, i have to have the box in the row i want transferred to be selected. Is it possible to make iit so that it deletes the data in sheet1 and so that it can move everything accordingly with one click of the button without having to be on a specific cell?

 

[mumps]

We need some way to reference the row that you want to copy. Since that row can be any row at random, the only way to do this with the "button" approach is to tell the macro that you want to reference the active row by selecting any cell in that row. This macro will copy then delete the active row:

Sub CopyRow()
    ActiveCell.EntireRow.Copy Sheets("AMP " & ActiveCell.Value).Cells(Sheets("AMP " & ActiveCell.Value).Rows.Count, "A").End(xlUp).Offset(1, 0)
    Rows(ActiveCell.Row).EntireRow.Delete
End Sub

If you want to use the first approach I suggested, then try this version:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Target.EntireRow.Copy Sheets("AMP " & Target.Value).Cells(Sheets("AMP " & Target.Value).Rows.Count, "A").End(xlUp).Offset(1, 0)
    Target.EntireRow.Delete
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[DeFox98]

Alright, those will work then. I apologize if i was a bit of a pain in the ***, I just dont know what all is possible and what isnt. Thank you for your time and effort, I would have never figured any of this out on my own. Ill try them both and see which one I like better. Thanks again.

 

[mumps]

You are very welcome.