Hello everyone!

I am looking for a way to consolidate data faster. My script works fine but it takes unfortunately very long. So I wonder if someone can give me some advice on how to make it work faster. My program is rather big with lots of modules so I rather describe what it does and only post the important code.

Background Information:
The goal of my program is to create a standardized looking diagram that shows stock data. I have raw data of about 260.000 rows and 31 columns (Sheet “raw_data”). The raw data consists of stock data like product type, product group, location name, location country, year, quantity, value etc. On another sheet called “Analysis” the user can set filters and select data for the y- and x-axis (i.e. quantity of a certain product in a particular location from 2012-2015).

How does my program work?
The user selects filters and the autofilter is applied to the raw data. After that, the none hidden rows and columns for the y-axis of the diagram are copied to new sheet called “temp”.

Example: Column “location country” is filter for “Italy” and “value” is selected as data type. Finally the y-axis is selected to show the product groups. That means that three columns are copied to a new sheet called “temp” (1. Product groups, 2. Value, 3. Year).

What is described before works fine and rather quickly. So here is where it starts:

For creating a diagram I need to consolidate the data that was selected and copied to the sheet "temp".

Product group Value Year
... ... ...
... ... ...

The result should looks like this:

Product Group 2012 2013 2014 2015
A * * * *
B * * * *
*sum of values

I use the sumifs function to consolidate the data. See my code extract here: 

Sub Consolidate_data()

Dim Column_A_end As Long

Application.ScreenUpdating = False
Application.EnableEvents = False

    With Worksheets("temp")
        Column_A_end = .Range("A1000000").End(xlUp).Row
        .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Copy Destination:=.Range("D1")
        .Range("D:D").RemoveDuplicates Columns:=1, Header:=xlNo
        .Range("A1:C1").Copy Destination:=.Range("D1")
        .Range("E1") = years(1)
        .Range("E1:K1").DataSeries Rowcol:=xlRows, Type:=xlLinear, Date:=xlDay, step:=1, Trend:=False 'Years 2009-2015
        .Columns("A:F").AutoFit
        .Range("E2").FormulaR1C1 = "=SUMIFS(R2C2:R" & Column_A_end & "C2,R2C1:R" & Column_A_end & "C1,""=""&RC4,R2C3:R" & Column_A_end & "C3,""=""&R1C)"
        If .Range("D1").End(xlDown).Row > 2 Then
            .Range("E2").AutoFill Destination:=.Range("E2:E" & .Range("D1").End(xlDown).Row), Type:=xlFillDefault
            .Range("E2:E" & .Range("D1").End(xlDown).Row).AutoFill Destination:=.Range("E2:K" & .Range("D1").End(xlDown).Row), Type:=xlFillDefault
        ElseIf .Range("D1").End(xlDown).Row = 2 Then
            .Range("E2").AutoFill Destination:=.Range("E2:K2"), Type:=xlFillDefault
        End If
        .Calculate
        .Columns("E:K").Copy
        .Range("E1").PasteSpecial Paste:=xlPasteValues
        .Columns("A:C").Delete
    End With

Worksheets("temp").Range("1:1").NumberFormat = "@"
Worksheets("temp").Columns("A:Z").AutoFit
            
End Sub
Sometimes I have data of more than 100.000 rows to be consolidated which can last > 10 min on my machine. Any idea how to make it faster? I appreciate any advice.

Thank you in advance.

Jack