With rows of data of a few thousand this runs in under a minute. However the data is now just over 100,000 and the macro makes excel not respond. Can anyone help. thanks
Sub DailyToMaster()
'
' DailyToMaster Macro
' Ctrl+Shift+Z
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Rows("1:4").Select
Selection.Delete Shift:=xlUp
Range("C:C,F:F,H:H,J:J,M:M,P:P,V:V,X:X,AA:AA,AC:AC").Select
Range("AC1").Activate
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("J:J").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Master").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Range("Table4[[#Headers],[Completed Date]]").Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Master").ListObjects("Table4").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Master").ListObjects("Table4").Sort.SortFields.Add _
Key:=Range("Table4[[#Headers],[Completed Date]]"), SortOn:=xlSortOnValues, _
Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Master").ListObjects("Table4").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B2").Select
ActiveCell.FormulaR1C1 = "=WEEKNUM([@[Completed Date]])"
Range("B3").Select
Range("Table4[Week Number]").FormulaR1C1 = _
"=WEEKNUM(Table4[@[Completed Date]])"
Columns("B:B").Select
Selection.NumberFormat = "General"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP([@SKU],plantable,4,FALSE)"
Range("F3").Select
Range("Table4[Planner number]").FormulaR1C1 = _
"=VLOOKUP(Table4[@SKU],plantable,4,FALSE)"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=[@[Qty Ordered]]-[@[Quantity Completed]]"
Range("J3").Select
Range("Table4[Variance]").FormulaR1C1 = _
"=Table4[@[Qty Ordered]]-Table4[@[Quantity Completed]]"
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
Range("W2").Select
ActiveCell.FormulaR1C1 = _
"=IF([@[ Amount Variance (material)]]<0,2,IF([@[ Amount Variance (material)]]>0,1,IF([@[ Amount Variance (material)]]=0,0)))"
Range("W3").Select
Range("Table4[Sort]").FormulaR1C1 = _
"=IF(Table4[@[ Amount Variance (material)]]<0,2,IF(Table4[@[ Amount Variance (material)]]>0,1,IF(Table4[@[ Amount Variance (material)]]=0,0)))"
Range("X2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS(variancecost,lot,[@[Lot '#]],masterweek,[@[Week Number]])"
Range("X3").Select
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
Range("Table4[True lot Variance]").FormulaR1C1 = _
"=SUMIFS(variancecost,lot,Table4[@[Lot '#]],masterweek,Table4[@[Week Number]])"
Range("Y2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS(variancecost,SKU,[@SKU],masterweek,[@[Week Number]])"
Range("Y3").Select
Range("Table4[True sku Variance]").FormulaR1C1 = _
"=SUMIFS(variancecost,SKU,Table4[@SKU],masterweek,Table4[@[Week Number]])"
Columns("T:V").Select
Selection.NumberFormat = "$#,##0.00"
Columns("X:Y").Select
Selection.NumberFormat = "$#,##0.00"
Columns("P:P").Select
Selection.NumberFormat = "$#,##0.00"
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Sheets("SKU COST PIVOT").Select
ActiveSheet.Unprotect
Sheets("Yeild Pivot").Select
ActiveSheet.Unprotect
Sheets("Material Pivot").Select
ActiveSheet.Unprotect
Sheets("Dashboard").Select
ActiveSheet.Unprotect
ActiveWorkbook.RefreshAll
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("Material Pivot").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
Sheets("Yeild Pivot").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
Sheets("SKU COST PIVOT").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
Sheets("Daily").Select
Selection.ClearContents
Sheets("Master").Select
Application.EnableEvents = True
Application.DisplayStatusBar = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Sub DailyToMaster()
'
' DailyToMaster Macro
' Ctrl+Shift+Z
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Rows("1:4").Select
Selection.Delete Shift:=xlUp
Range("C:C,F:F,H:H,J:J,M:M,P:P,V:V,X:X,AA:AA,AC:AC").Select
Range("AC1").Activate
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("J:J").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Master").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Range("Table4[[#Headers],[Completed Date]]").Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Master").ListObjects("Table4").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Master").ListObjects("Table4").Sort.SortFields.Add _
Key:=Range("Table4[[#Headers],[Completed Date]]"), SortOn:=xlSortOnValues, _
Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Master").ListObjects("Table4").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B2").Select
ActiveCell.FormulaR1C1 = "=WEEKNUM([@[Completed Date]])"
Range("B3").Select
Range("Table4[Week Number]").FormulaR1C1 = _
"=WEEKNUM(Table4[@[Completed Date]])"
Columns("B:B").Select
Selection.NumberFormat = "General"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP([@SKU],plantable,4,FALSE)"
Range("F3").Select
Range("Table4[Planner number]").FormulaR1C1 = _
"=VLOOKUP(Table4[@SKU],plantable,4,FALSE)"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=[@[Qty Ordered]]-[@[Quantity Completed]]"
Range("J3").Select
Range("Table4[Variance]").FormulaR1C1 = _
"=Table4[@[Qty Ordered]]-Table4[@[Quantity Completed]]"
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
Range("W2").Select
ActiveCell.FormulaR1C1 = _
"=IF([@[ Amount Variance (material)]]<0,2,IF([@[ Amount Variance (material)]]>0,1,IF([@[ Amount Variance (material)]]=0,0)))"
Range("W3").Select
Range("Table4[Sort]").FormulaR1C1 = _
"=IF(Table4[@[ Amount Variance (material)]]<0,2,IF(Table4[@[ Amount Variance (material)]]>0,1,IF(Table4[@[ Amount Variance (material)]]=0,0)))"
Range("X2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS(variancecost,lot,[@[Lot '#]],masterweek,[@[Week Number]])"
Range("X3").Select
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
Range("Table4[True lot Variance]").FormulaR1C1 = _
"=SUMIFS(variancecost,lot,Table4[@[Lot '#]],masterweek,Table4[@[Week Number]])"
Range("Y2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS(variancecost,SKU,[@SKU],masterweek,[@[Week Number]])"
Range("Y3").Select
Range("Table4[True sku Variance]").FormulaR1C1 = _
"=SUMIFS(variancecost,SKU,Table4[@SKU],masterweek,Table4[@[Week Number]])"
Columns("T:V").Select
Selection.NumberFormat = "$#,##0.00"
Columns("X:Y").Select
Selection.NumberFormat = "$#,##0.00"
Columns("P:P").Select
Selection.NumberFormat = "$#,##0.00"
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Sheets("SKU COST PIVOT").Select
ActiveSheet.Unprotect
Sheets("Yeild Pivot").Select
ActiveSheet.Unprotect
Sheets("Material Pivot").Select
ActiveSheet.Unprotect
Sheets("Dashboard").Select
ActiveSheet.Unprotect
ActiveWorkbook.RefreshAll
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("Material Pivot").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
Sheets("Yeild Pivot").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
Sheets("SKU COST PIVOT").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
Sheets("Daily").Select
Selection.ClearContents
Sheets("Master").Select
Application.EnableEvents = True
Application.DisplayStatusBar = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub