Inventory list- copy row based on cell value to another sheet

[Addiaddi1]

I am working on this inventory list where I need to copy data from Sheet1 and Sheet2 to Sheet 3 named “Total” based on cell value. There is one tricky part that I cannot overcome. Here is a more sample example of my Excel sheet:

In “Sheet1” I have a list of items like this one

In “Sheet2” I have another similar list like this one

And when I write a number in the Order cell, the row needs to be copied to sheet “Total” in the first available row like this. The code needs to be able to stack the rows from both "Sheet1" and "Sheet2" to the final sheet "Total".

But here comes the part where I am lost, and I am counting on you guys. If I change the order number in “Sheet1” or “Sheet2” I would want the “Total” to change also. So basically if I decided to order 10 T-shirts instead of 2 I could go back to “Sheet1” and change the order number and the “Total” sheet would change also.

Hope someone can help me with this, I would be infinitely grateful. Amazing if you guys could post your Excel sheet if your are able to find a solution to this!

 

[MrTomasz]

Not sure if I get what you need, but you can try use sumif function in Total sheet in columd D:

=sumif(Sheet1!A:A;A2;Sheet1!D:D)+sumif(Sheet2!A:A;A2;Sheet2!D:D)

Thanks of that, once the number is changed in sheet1 or 2, it will be changed in total sheet as well.

 

[vcoolio]

Hello Addiaddi1,

The following may help:-

1) Place this code in a standard module:-

Sub Test()

Dim ws As Worksheet

Application.ScreenUpdating = False
Application.EnableEvents = False

Sheet1.UsedRange.Offset(1).Clear

        For Each ws In Worksheets
               If ws.Name <> "Total" Then
                     With ws.[A1].CurrentRegion
                            .AutoFilter 4, "<>" & ""
                            .Offset(1).EntireRow.Copy Sheet1.Range("A" & Rows.Count).End(3)(2)
                            .AutoFilter
                     End With
               End If
        Next ws

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

2) Place this code in the ThisWorkbook module:-

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Intersect(Target, Sh.Columns("D:D")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Test

End Sub

To implement the second code above:-
- Right click on the Total tab.
- Select 'View Code" from the menu that appears. This will open the VB Editor.
- Over to the left in the Project Explorer, double click on ThisWorkbook.
- In the big white code field, paste the above code.

The second code, which is a SheetChange event code and will operate in all source sheets, calls the first code above ("Test") which filters for non-blank cells in Column D of each source sheet and then transfers the relevant rows of data to the Total sheet. If you make changes to any order quantity in Column D of any source sheet, the Total sheet will refresh and any changes will be updated and displayed.

I hope that this helps.

Cheerio,
vcoolio.

 

[Addiaddi1]

Hello Addiaddi1,

The following may help:-

1) Place this code in a standard module:-

Sub Test()

Dim ws As Worksheet

Application.ScreenUpdating = False
Application.EnableEvents = False

Sheet1.UsedRange.Offset(1).Clear

        For Each ws In Worksheets
               If ws.Name <> "Total" Then
                     With ws.[A1].CurrentRegion
                            .AutoFilter 4, "<>" & ""
                            .Offset(1).EntireRow.Copy Sheet1.Range("A" & Rows.Count).End(3)(2)
                            .AutoFilter
                     End With
               End If
        Next ws

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

2) Place this code in the ThisWorkbook module:-

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Intersect(Target, Sh.Columns("D:D")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Test

End Sub

To implement the second code above:-
- Right click on the Total tab.
- Select 'View Code" from the menu that appears. This will open the VB Editor.
- Over to the left in the Project Explorer, double click on ThisWorkbook.
- In the big white code field, paste the above code.

The second code, which is a SheetChange event code and will operate in all source sheets, calls the first code above ("Test") which filters for non-blank cells in Column D of each source sheet and then transfers the relevant rows of data to the Total sheet. If you make changes to any order quantity in Column D of any source sheet, the Total sheet will refresh and any changes will be updated and displayed.

I hope that this helps.

Cheerio,
vcoolio.

That works! You are an absolute genius! Thank you so much, you made my week. A very clear and comprehensive answer in less than an hour! THANK YOU CHEERIO

 

[vcoolio]

You're welcome Addiaddi1.
I'm glad to have been able to assist.

Cheerio,
vcoolio.

 

[Addiaddi1]

You're welcome Addiaddi1.
I'm glad to have been able to assist.

Cheerio,
vcoolio.

Hello again Cherrio! And thank you again. I

When I tried to convert your code to my Excel template I was not able to make it work as well as in the example template that I made. So I ‘m going to re explain with the real template and ask few more questions if your are okay with that.

I have 5 sheets named as you can see in the photo.

“Front page” sheet- does not matter in this workbook
"Total" - Is the sheet where you can see all the products you have ordered.
“BB & TT” , “AA & TT” , “SS & GG” – are similar sheets with list of products that you can order as you can see here below.


“BB & TT”

“AA & TT”

“SS & GG”

When I insert a number into column H in sheet “BB & TT” , “AA & TT” , “SS & GG” I want the row to be copied to the sheet named “Total” where it get stacked up as you can see:

From this

To this

As I asked in my first question I need to be able to go to sheet “ AA & TT” or “BB & TT” as example and change the order quantity I column H and the sheet “Total” would then change also based on the new quantity number.

And if you are smart enough it would be amazing if you are able to write a code so I can also change the order quantity in column H in sheet “Total” and the number would then change in sheet “ SS & GG”, “BB & TT” or “AA & TT” based on the new quantity value. But that's not essential but it would be great.

As you can probably tell this is an order workbook for products that I am selling in my small company that I am starting. The quantity number you write in column H in sheets “SS & GG”, “BB & TT” or “AA & TT” is the quantity of product you want to order.

Hope you can help me with this :D And thank you again for your time.

 

[vcoolio]

Hello Addiaddi,

For the "Test" code: amended as follows:-

Sub Test()

Dim ws As Worksheet

Application.ScreenUpdating = False

Sheet1.UsedRange.Offset(1).Clear

For Each ws In Worksheets
       If ws.Name <> "Total" Then
              With ws.[A1].CurrentRegion
                      .AutoFilter 8, "<>" & ""
                      .Offset(1).EntireRow.Copy Sheet1.Range("A" & Rows.Count).End(3)(2)
                      .AutoFilter
              End With
        End If
Next ws

Application.ScreenUpdating = True

End Sub

For the Workbook_SheetChange code: amended as follows:-

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Intersect(Target, Sh.Columns("H:H")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Test

End Sub

Cheerio,
vcoolio.

 

[Addiaddi1]

Hello Addiaddi,

For the "Test" code: amended as follows:-

Sub Test()

Dim ws As Worksheet

Application.ScreenUpdating = False

Sheet1.UsedRange.Offset(1).Clear

For Each ws In Worksheets
       If ws.Name <> "Total" Then
              With ws.[A1].CurrentRegion
                      .AutoFilter 8, "<>" & ""
                      .Offset(1).EntireRow.Copy Sheet1.Range("A" & Rows.Count).End(3)(2)
                      .AutoFilter
              End With
        End If
Next ws

Application.ScreenUpdating = True

End Sub

For the Workbook_SheetChange code: amended as follows:-

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Intersect(Target, Sh.Columns("H:H")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Test

End Sub

Cheerio,
vcoolio.

You did it again! Thank you so much Cheerio :D
One last thing.

Before I enter the VBA code all the data is formatted as a table so I am able to reorder the list at any time.

But when I enter a number quantity in column H and the VBA does the changes in sheet "Total" that option disappear. I would need to keep the data format. Hope you understand what I mean.


And also.
In sheet "Total", I need the list to stack up from row A11 instead of the first available row.

Like this

Instead of this

Thank you again for your time and help. Your are a genius.

 

[vcoolio]

Hello Addiaddi,

If you are saying that you would like the drop down arrows to stay visible, then the code amended as follows should do:-

Sub Test()

Dim ws As Worksheet

Application.ScreenUpdating = False

Sheet1.ListObjects("Table1").Unlist
Sheet1.[A10].CurrentRegion.Offset(1).Clear

        For Each ws In Worksheets
               If ws.Name <> "Total" Then
                      With ws.[A1].CurrentRegion
                              .AutoFilter 8, "<>" & ""
                              .Offset(1).EntireRow.Copy Sheet1.Range("A" & Rows.Count).End(3)(2)
                              .AutoFilter
                      End With
                      ws.[H1].AutoFilter
                End If
        Next ws

Sheet1.Select
Sheet1.ListObjects.Add(xlSrcRange, Cells(10, 1).CurrentRegion, , xlYes).Name = "Table1"

Application.ScreenUpdating = True

End Sub

Assign this code to a button on the Total sheet. I have forgone the Workbook_SheetChange code as it wasn't really necessary. Once you have made or updated your entries in the source sheets, go to the Total sheet and click on the button. The Total sheet will then update.

I've attached a mock-up workbook to show you how it works.

Cheerio,
vcoolio.

Addiaddi