Macro to save the information accumulated by product, but with different weights available

[K0st4din]

Hello everyone,
please you for your assistance to make a macro that automates the actions that I do manually.
Each month, receive information about products sold, which copy and put in a master file. I have over 80 separate sheets with the names of each product. Once the information transmitted for example January, I save the file and wait for the information to come next month. Then filter the new month and start again manually choose products (!!! very important - have the same products but with different weight in grams) and put the information in the last line of the previous month, ie should be cumulative. Attach sample file and give a link if you want to watch and video.
Thank you in advance for your cooperation on your part.
Link to file
Link to video

 

[K0st4din]

Friends whether it is possible to make such a macro?

 

[K0st4din]

This macro I did it manually, but it's so stupid that made me ashamed of it. For all the selected cells are manually marked and should be automatic search containing the word and the word from each sheet and then bring it to him

Sub Macro1()'
' Macro1 Macro
'


'
    ActiveSheet.Range("$A$1:$L$56").AutoFilter Field:=1, Operator:= _
        xlFilterValues, Criteria2:=Array(1, "1/1/2014")
    ActiveSheet.Range("$A$1:$L$56").AutoFilter Field:=3, Criteria1:= _
        "=Apples london 100 mg.", Operator:=xlOr, Criteria2:= _
        "=Apples london 800 mg"
    Range("A10:L28").Select
    Selection.Copy
    Sheets("Apples").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("A6").Select
    Sheets("Sheet1").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$L$56").AutoFilter Field:=3, Criteria1:= _
        "=Apricot London 1000 g", Operator:=xlOr, Criteria2:= _
        "=Apricot London 800 mg"
    Range("A12:L20").Select
    Selection.Copy
    Sheets("Apricot").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("A5").Select
    Sheets("Sheet1").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$L$56").AutoFilter Field:=3, Criteria1:= _
        "=grapes london 150 grams.", Operator:=xlOr, Criteria2:= _
        "=Grapes london 300 grams."
    Range("A2:L22").Select
    Selection.Copy
    Sheets("Grapes").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("A6").Select
    Sheets("Sheet1").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$L$56").AutoFilter Field:=3, Criteria1:=Array( _
        "Peaches london 25 mg", "Peaches london 250 mg", "Peaches london 50 mg."), _
        Operator:=xlFilterValues
    Range("A7:L27").Select
    Selection.Copy
    Sheets("Peaches").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("A11").Select
    Sheets("Sheet1").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$L$56").AutoFilter Field:=3, Criteria1:=Array( _
        "tomatoes London 1000 kg", "Tomatoes London 4000 mg.", "Tomatoes london 500 mg") _
        , Operator:=xlFilterValues
    Range("A4:L23").Select
    Selection.Copy
    Sheets("Tomatoes").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("A9").Select
    Sheets("Sheet1").Select
    Application.CutCopyMode = False
    Range("A4").Select
    ActiveSheet.Range("$A$1:$L$56").AutoFilter Field:=1, Operator:= _
        xlFilterValues, Criteria2:=Array(1, "2/1/2014")
    ActiveSheet.Range("$A$1:$L$56").AutoFilter Field:=3, Criteria1:= _
        "=Apples london 100 mg.", Operator:=xlOr, Criteria2:= _
        "=Apples london 800 mg"
    Range("A37:L55").Select
    Selection.Copy
    Sheets("Apples").Select
    Range("A6").Select
    ActiveSheet.Paste
    Range("A10").Select
    Sheets("Sheet1").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$L$56").AutoFilter Field:=3, Criteria1:= _
        "=Apricot London 1000 g", Operator:=xlOr, Criteria2:= _
        "=Apricot London 800 mg"
    Range("A39:L47").Select
    Selection.Copy
    Sheets("Apricot").Select
    Range("A5").Select
    ActiveSheet.Paste
    Range("A8").Select
    Sheets("Sheet1").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$L$56").AutoFilter Field:=3, Criteria1:= _
        "=grapes london 150 grams.", Operator:=xlOr, Criteria2:= _
        "=Grapes london 300 grams."
    Range("A29:L49").Select
    Selection.Copy
    Sheets("Grapes").Select
    Range("A6").Select
    ActiveSheet.Paste
    Range("A10").Select
    Sheets("Sheet1").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$L$56").AutoFilter Field:=3, Criteria1:=Array( _
        "Peaches london 25 mg", "Peaches london 250 mg", "Peaches london 50 mg."), _
        Operator:=xlFilterValues
    Range("A34:L54").Select
    Selection.Copy
    Sheets("Peaches").Select
    Range("A11").Select
    ActiveSheet.Paste
    Range("A20").Select
    Sheets("Sheet1").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$L$56").AutoFilter Field:=3, Criteria1:=Array( _
        "tomatoes London 1000 kg", "Tomatoes London 4000 mg.", "Tomatoes london 500 mg") _
        , Operator:=xlFilterValues
    Range("A31:L50").Select
    Selection.Copy
    Sheets("Tomatoes").Select
    Range("A9").Select
    ActiveSheet.Paste
    Range("A16").Select
    Sheets("Sheet1").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$L$56").AutoFilter Field:=3
    ActiveSheet.Range("$A$1:$L$56").AutoFilter Field:=1
    Range("A2").Select
End Sub

 

[K0st4din]

Since there is no answer - probably not clear my question:
Nanyasyam yourself new month with the products in the table, then the macro to carry each sheet product and its variations in weights, by copying the entire rows of information.
Thank you again.

 

[K0st4din]

Hello again all, or is obviously impossible to make such a macro or you can help me. Therefore I ask for help and in this site. I hope there is someone who can help me.
Yet if something is not clear ask me.

 

[K0st4din]

Any ideas friends?
Is there anything that is not clear?
Thank you

 

[mancubus]

replied here:
Macro that to transfer information sheets by product

check if it works for you.

 

[mancubus]

for those who may be interested in my solution:

assumptions:
1) the leftmost worksheet contains consolidated data of products sold.
2) each month you want to split previous month's data. (so whey you run below code now, it will split 2014-November's sales data.)

Sub Split_Monthly_Sales_To_Product_Worksheets()


    Dim ProductNames As String
    Dim Products
    Dim i As Integer
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With


    For i = 2 To Worksheets.Count
        ProductNames = ProductNames & "|" & Worksheets(i).Name
    Next


    Products = Split(Mid(ProductNames, 2), "|")
    
    With Worksheets(1)
        .AutoFilterMode = False
        .Cells(1).AutoFilter Field:=1, Criteria1:=xlFilterLastMonth, Operator:=xlFilterDynamic
        For i = LBound(Products) To UBound(Products)
            .Cells(1).AutoFilter Field:=3, Criteria1:="=*" & Products(i) & "*"
            With .AutoFilter.Range
                If .Rows.Count > 1 Then
                    .Offset(1).Copy Worksheets(Products(i)).Range("A" & Rows.Count).End(xlUp).Offset(1)
                End If
            End With
        Next i
        .AutoFilterMode = False
    End With


End Sub