Macro running slow/not responding and cant handle the expanding amounts of data

D

dellsher

New Member
Joined
Nov 29, 2017
Messages
5
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Re: Macro running slow/not resopnding and cant handle the expanding amounts of data

Welcome to the Board!

I can tell that the Macro was recorded. Recorded code often requires some clean-up to make it run more efficiently. The issue is that recorded code is very literal, and records every action. Certain actions, like Scrolling are not needed at all and can be eliminated altogether. Also, "Select" statements slow the code down, and are often not needed. Just about anytime you have one line ending in ".Select", and you have the next row starting with "Selection" or "ActiveCell", those two rows can be combined together.

For example, this:
Code: 
[COLOR=#333333]Columns("B:B").Select[/COLOR]
[COLOR=#333333]Selection.NumberFormat = "General"[/COLOR]
can be combined to this:
Columns("B:B").NumberFormat = "General"

And this:
Code: 
[COLOR=#333333]Range("Y2").Select[/COLOR]
[COLOR=#333333]ActiveCell.FormulaR1C1 = _[/COLOR]
[COLOR=#333333]"=SUMIFS(variancecost,SKU,[@SKU],masterweek,[@[Week Number]])"[/COLOR]
can be combined to this:
Code: 
[COLOR=#333333]Range("Y2").[/COLOR][COLOR=#333333]FormulaR1C1 = _[/COLOR]
[COLOR=#333333]"=SUMIFS(variancecost,SKU,[@SKU],masterweek,[@[Week Number]])"[/COLOR]

So, we can clean up a lot of your code, and condense it down to this:
Code: 
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").Delete Shift:=xlUp
Range("C:C,F:F,H:H,J:J,M:M,P:P,V:V,X:X,AA:AA,AC:AC").Delete Shift:=xlToLeft
Columns("H:H").Cut
Columns("A:A").Insert Shift:=xlToRight
Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("J:J").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("1:1").Delete Shift:=xlUp
Rows("1:1").Select
Range(Selection, Selection.End(xlDown)).Copy

Sheets("Master").Select
Rows("2:2").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").FormulaR1C1 = "=WEEKNUM([@[Completed Date]])"
Range("Table4[Week Number]").FormulaR1C1 = _
    "=WEEKNUM(Table4[@[Completed Date]])"
Columns("B:B").NumberFormat = "General"
Range("F2").FormulaR1C1 = "=VLOOKUP([@SKU],plantable,4,FALSE)"
Range("Table4[Planner number]").FormulaR1C1 = _
    "=VLOOKUP(Table4[@SKU],plantable,4,FALSE)"
Range("J2").FormulaR1C1 = "=[@[Qty Ordered]]-[@[Quantity Completed]]"
Range("Table4[Variance]").FormulaR1C1 = _
    "=Table4[@[Qty Ordered]]-Table4[@[Quantity Completed]]"
Range("W2").FormulaR1C1 = _
    "=IF([@[ Amount Variance (material)]]<0,2,IF([@[ Amount Variance (material)]=]=>0,1,IF([@[ Amount Variance (material)]]=0,0)))"
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").FormulaR1C1 = _
    "=SUMIFS(variancecost,lot,[@[Lot '#]],masterweek,[@[Week Number]])"
Range("Table4[True lot Variance]").FormulaR1C1 = _
    "=SUMIFS(variancecost,lot,Table4[@[Lot '#]],masterweek,Table4[@[Week Number]])"
Range("Y2").FormulaR1C1 = _
    "=SUMIFS(variancecost,SKU,[@SKU],masterweek,[@[Week Number]])"
Range("Table4[True sku Variance]").FormulaR1C1 = _
    "=SUMIFS(variancecost,SKU,Table4[@SKU],masterweek,Table4[@[Week Number]])"

Columns("T:V").NumberFormat = "$#,##0.00"
Columns("X:Y").NumberFormat = "$#,##0.00"
Columns("P:P").NumberFormat = "$#,##0.00"

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").ClearContents
Sheets("Master").Select

Application.EnableEvents = True
Application.DisplayStatusBar = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub
See if that helps at all.
 
Upvote 0
Re: Macro running slow/not resopnding and cant handle the expanding amounts of data

Thanks For replying.
there is a small issue with this but that's for cleaning it up.
however its bugging at the line I have underlined and bolded.
any quick fixes to stop the bug ? thanks again

Range("B2").FormulaR1C1 = "=WEEKNUM([@[Completed Date]])"
Range("Table4[Week Number]").FormulaR1C1 = _
"=WEEKNUM(Table4[@[Completed Date]])"
Columns("B:B").NumberFormat = "General"
Range("F2").FormulaR1C1 = "=VLOOKUP([@SKU],plantable,4,FALSE)"
Range("Table4[Planner number]").FormulaR1C1 = _
"=VLOOKUP(Table4[@SKU],plantable,4,FALSE)"
Range("J2").FormulaR1C1 = "=[@[Qty Ordered]]-[@[Quantity Completed]]"
Range("Table4[Variance]").FormulaR1C1 = _
"=Table4[@[Qty Ordered]]-Table4[@[Quantity Completed]]"
Range("W2").FormulaR1C1 = _
"=IF([@[ Amount Variance (material)]]<0,2,IF([@[ Amount Variance (material)]=]=>0,1,IF([@[ Amount Variance (material)]]=0,0)))"
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").FormulaR1C1 = _
"=SUMIFS(variancecost,lot,[@[Lot '#]],masterweek,[@[Week Number]])"
Range("Table4[True lot Variance]").FormulaR1C1 = _
"=SUMIFS(variancecost,lot,Table4[@[Lot '#]],masterweek,Table4[@[Week Number]])"
Range("Y2").FormulaR1C1 = _
"=SUMIFS(variancecost,SKU,[@SKU],masterweek,[@[Week Number]])"
Range("Table4[True sku Variance]").FormulaR1C1 = _
"=SUMIFS(variancecost,SKU,Table4[@SKU],masterweek,Table4[@[Week Number]])"
 
Upvote 0
Re: Macro running slow/not resopnding and cant handle the expanding amounts of data

What exactly is the bug?
You'll have to forgive me. I don't use Tables very much, so I am not all that familiar with the Table notation. This part looks a little funny to me though:
Code: 
[B][U]IF([@[ Amount Variance (material)][COLOR=#ff0000]=[/COLOR]]=>0[/U][/B]
I don't understand the equal sign between the two right brackets.
Also, if you are trying to use Greater than or Equal to, ">=" is the correct structure to write that, not "=>".
 
Upvote 0
Re: Macro running slow/not resopnding and cant handle the expanding amounts of data

I think you right, I’m away from my desk at the moment but comparing formula to the one in the code the equals sign shouldn’t be there as the <0,2,if is the same sum and it dosnt have the equals sign. I’ll adjust this when I’m back and let you know.
Thanks
 
Upvote 0
Re: Macro running slow/not resopnding and cant handle the expanding amounts of data

That is running a lot smother now.

Thanks you were a great help
 
Upvote 0
Re: Macro running slow/not resopnding and cant handle the expanding amounts of data

You are welcome!
I am glad to hear that it is working well now.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
formula - vba macro
2
Replies
12
Views
1K
Micron
M
J
  • Solved
Run a Macro if cell in current Row has any value
Replies
2
Views
949
jmpatrick
J
Tim_D
  • Question Question
VBA Structure Best Practices and Code Review
2
Replies
13
Views
6K
dmt32
D
tlc53
  • Solved
VBA Code - Closing Statement Wrong Perhaps
Replies
2
Views
760
tlc53
tlc53
willow1985
  • Question Question
Macro freezes excel workbook when executed via shortcut (button) but runs fine from VB
Replies
5
Views
5K
nardagus
nardagus

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top