Moving Complete Rows Based On Cell Value In Column D

[marwood]

Hi All,
I am trying to complete a parts ordering system for work with my limited excel knowledge.
I currently have 15 worksheets with various names & each sheet has 5 x columns: A=Item B=Description C=Cost D=Order E=Total.
What I need to happen is when a quantity is entered into Column D, that the complete row with the value added is automatically copied to the first worksheet & if further items ordered it goes to next row down.... I have attached a few pics of what my sheets look like...Is this possible?
Any help or feedback would be greatly appreciated...

https://i1249.photobucket.com/albums/hh502/marwood3/Order Sheet_zpstwvrsaov.png
http://i1249.photobucket.com/albums/hh502/marwood3/LTK60_zpsqozebbny.png
http://i1249.photobucket.com/albums/hh502/marwood3/W-Swivels_zpscboiybi9.png

​

 

[My Aswer Is This]

Re: Help Moving Complete Rows Based On Cell Value In Column D

So you want this same script to work on all 14 sheets and always copy the row to Sheet(1)?

 

[marwood]

Re: Help Moving Complete Rows Based On Cell Value In Column D

greatly appreciate the reply. yes that is correct, each time a value is entered into Column D on any of the 14 worksheets it automatically send this row to sheet 1...

 

[My Aswer Is This]

Re: Help Moving Complete Rows Based On Cell Value In Column D

This script will work in all sheets in your workbook except for sheet(1)
All the rows with a value change in column D will be copied to sheet(1)
This will occur automatically when you manually change a value in column D of any sheet except sheet(1)

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
In upper left corner of screen double click on ThisWorkbook and paste in this code

Paste the code in the VBA edit window

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Modified  1/10/2019  3:08:49 AM  EST
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As String
Dim Lastrow As Long
Dim Thissheet As String
ans = Sheets(1).Name
Thissheet = ActiveSheet.Name
If Thissheet <> ans Then
    If Target.Column = 4 Then
        Lastrow = Sheets(ans).Cells(Rows.Count, "D").End(xlUp).Row + 1
        Target.EntireRow.Copy Sheets(ans).Rows(Lastrow)
    End If
End If
End Sub

 

[marwood]

Re: Help Moving Complete Rows Based On Cell Value In Column D

This script will work in all sheets in your workbook except for sheet(1)
All the rows with a value change in column D will be copied to sheet(1)
This will occur automatically when you manually change a value in column D of any sheet except sheet(1)

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
In upper left corner of screen double click on ThisWorkbook and paste in this code

Paste the code in the VBA edit window

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Modified  1/10/2019  3:08:49 AM  EST
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As String
Dim Lastrow As Long
Dim Thissheet As String
ans = Sheets(1).Name
Thissheet = ActiveSheet.Name
If Thissheet <> ans Then
    If Target.Column = 4 Then
        Lastrow = Sheets(ans).Cells(Rows.Count, "D").End(xlUp).Row + 1
        Target.EntireRow.Copy Sheets(ans).Rows(Lastrow)
    End If
End If
End Sub

Many thanks for the effort in trying to help me resolve this issue I have.
I entered as code as mentioned below and saved workbook as [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Microsoft Excel Macro-Enabled Worksheet (.xlsm)

I enter a value in column D with no results as far? Is there something simple I am missing? I have little to no knowledge of codes & formulas apart from the very basics...
Screenshot of code entered...

_{^{<strike>
</strike>}}[/FONT]

 

[My Aswer Is This]

Re: Help Moving Complete Rows Based On Cell Value In Column D

Your image shows you did not put this script into ThisWorkbook Like I explained in previous Post.

You must double click on ThisWorkbook

Then Paste in the code.

You should see Thisworkbook in upper left area of screen.

Thisworkbook means it will operate in all sheets in workbook.

But my code tells it not to work if you change a value in column D of Sheet(1)

 

[marwood]

Re: Help Moving Complete Rows Based On Cell Value In Column D

Your image shows you did not put this script into ThisWorkbook Like I explained in previous Post.

You must double click on ThisWorkbook

Then Paste in the code.

You should see Thisworkbook in upper left area of screen.

Thisworkbook means it will operate in all sheets in workbook.

But my code tells it not to work if you change a value in column D of Sheet(1)

Many thanks for the reply. After a bit of googling i worked out how to get the VBA tab to the side and clicked on ThisWorkbook tab and entered code. (screenshot below)..
It worked really well thank you... but the only thing it does not do it when I delete the value from Column D the copied row remains? can this be modified?

 

[marwood]

forgot screenshot

 

[My Aswer Is This]

Re: Help Moving Complete Rows Based On Cell Value In Column D

I do not understand this:

when I delete the value from Column D the copied row remains?​

Your original post said:

What I need to happen is when a quantity is entered into Column D, that the complete row with the value added is automatically copied to the first worksheet & if further items ordered it goes to next row down....

So you want what deleted from where?

You want the row deleted from Sheet(1)??


​

 

[marwood]

With code