Excel crashes when playing code, but is fine when stepped through

[JWatson220]

Hey,

Long time reader, first time poster.

I'm trying to play the below code, but when I do it crashes, when I step through the code it works fine.

Any ideas would be greatly appreciated.

Many thanks

Dim iMonth, sPath, File, sFile, vFile, mFile, Fund, iFile, FileType As String
Dim row, NumFunds, Lastrow As Integer
Dim wb As Workbook

'turn off pop-ups
    Application.DisplayAlerts = False

'Sets month as variable
    Sheets("Home").Select
    iMonth = Range("E4").Value

'sets macrobook name as variable
    vFile = ActiveWorkbook.Name

'set row number as variable
    row = 2

'Set number of funds as variable
    Sheets("File names").Select
    With ActiveSheet
        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).row
    End With
    NumFunds = Lastrow + 1
    
Do

'Set fund ICON code as variable
    Sheets("File names").Select
    Fund = Range("E" & row).Value

'Sets transaction data file path & names as variable
    Sheets("Data tables").Select
    sPath = Range("K5").Value
    iFile = Range("K3").Value
    FileType = Range("K6").Value
    sFile = sPath & iMonth & "" & Fund & FileType

'Open first excel transaction file
    Set wb = Workbooks.Open(sFile)
    
'Set transaction file as variable
    mFile = ActiveWorkbook.Name
    
'Select sheet 5 and copy entire contents
    Sheets("5-RawTRANSACTIONS0").Select
    Cells.Select
    Selection.Copy

'Select macrobook and paste into BG transactions raw tab
    Windows(vFile).Activate
    Sheets("BG Transactions raw").Select
    Range("A1").Select
    ActiveSheet.Paste

'Close valuation file
    Windows(mFile).Activate
    ActiveWindow.Close
    
' select raw data tab and select & copy relatvie columns, before pasting into workings tab
    Windows(vFile).Activate
    Sheets("BG Transactions raw").Select
    Range("C:C,H:H,J:K,N:N,R:R,V:V,AS:AS,BJ:BJ").Select
    Selection.Copy
    Sheets("Workings - BG trans").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
' Set trade & settlement date columns as date format
    Columns("D:E").Select
    Selection.NumberFormat = "m/d/yyyy"

'Add column to end of table called "category"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Category"

'Add vlookup to new column to determine country category
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNUMBER(SEARCH("" GDR"",RC[-8]))=TRUE,""Euroclear ""&RC[-7],IF(RC[-3]="""",VLOOKUP((RC[-3]&RC[-1]),'Data table'!C:C[3],4,FALSE)&"" CA"",VLOOKUP((RC[-3]&RC[-1]),'Data table'!C:C[3],4,FALSE)&"" ""&RC[-7]))"

'Reapply formula to all data rows
    Selection.Copy
    ActiveCell.Offset(0, -1).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Selection.End(xlUp).Select
    
'identify transactions which settled in other months
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "Remove data"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(MONTH(RC[-6])<>(VLOOKUP(Home!R4C5,'Data table'!R1C15:R13C16, 2, FALSE)), ""1"", ""2"")"

'Reapply formula to all data rows
    Selection.Copy
    ActiveCell.Offset(0, -1).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Selection.End(xlUp).Select

'Apply filter and delete transactions that settled in other month
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Workings - BG trans").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Workings - BG trans").AutoFilter.Sort.SortFields.Add Key:= _
        Range("K1:K50000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Workings - BG trans").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Range("$A$1:$K$50000").AutoFilter Field:=11, Criteria1:="1"
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.EntireRow.Delete
    ActiveWorkbook.Worksheets("Workings - BG trans").AutoFilter.Sort.SortFields.Clear
    On Error Resume Next
    ActiveSheet.ShowAllData
    Range("K1").Select
    
'Delete column to identify transactions settling in other months
    Columns("K:K").Select
    Selection.Delete Shift:=xlToLeft

'if statement to determine if there are no transactions
If Range("A2").Value = "" Then GoTo skip1


' sort data based on Category
    ActiveWorkbook.Worksheets("Workings - BG trans").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Workings - BG trans").AutoFilter.Sort.SortFields.Add Key:= _
        Range("J1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Workings - BG trans").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
' autofit data table
    Cells.Select
    Cells.EntireColumn.AutoFit
    Application.CutCopyMode = False

'Create pivot table using formatted data
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Workings - BG trans!R1C1:R50000C10", Version:=6).CreatePivotTable TableDestination:= _
        "", TableName:="PivotTable1", DefaultVersion:=6
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Category")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("QUANTITY"), "Sum of QUANTITY", xlSum
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of QUANTITY")
        .Caption = "Count of QUANTITY"
        .Function = xlCount
    End With

'name new sheet pivot table
    ActiveSheet.Name = "Pivot table 2"
    
'Select checking s/s and go to tab for 1st Fund on File names tab
    Windows(iFile).Activate
    Sheets(Fund).Select

'Input formula to vlookup BG Purchase data
    Range("K10").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP((RC[-10]&"" Purchases""),'[" & vFile & "]Pivot table 2'!R1C1:R5000C2, 2, FALSE), ""ZERO"")"
    Selection.Copy
    ActiveCell.Offset(0, -1).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste

'Paste special values overtop of BG Purchase trans formula
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'Remove all zero balances from BG Purchase data VLOOKUP
    Selection.Replace What:="ZERO", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
'input formula to vlookup BG sales transaction data
    Range("M10").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP((RC[-12]&"" Sales""), '[" & vFile & "]Pivot table 2'!R1C1:R5000C2, 2, FALSE), ""ZERO"")"
    Selection.Copy
    ActiveCell.Offset(0, -3).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 3).Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste

'Paste special values overtop of BG sales trans formula
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'Remove all zero balances from BG sales trans data VLOOKUP
    Selection.Replace What:="ZERO", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

'input formula to vlookup BG CA trans data
    Range("O10").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP((RC[-14]&"" CA""), '[" & vFile & "]Pivot table 2'!R1C1:R5000C2, 2, FALSE), ""ZERO"")"
    Selection.Copy
    ActiveCell.Offset(0, -5).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 5).Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste

'Paste special values overtop of BG CA trans formula
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'Remove all zero balances from BG CA trans data VLOOKUP
    Selection.Replace What:="ZERO", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

'Select Macrobook and copy detailed transaction data into Fund - transaction tab in checking s/s
    Windows(vFile).Activate
    Sheets("Workings - BG trans").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows(iFile).Activate
    Sheets(Fund & "-Transactions").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'Apply Subtotal where at each change in Category count category
    Application.CutCopyMode = False
    Selection.Subtotal GroupBy:=10, Function:=xlCount, TotalList:=Array(10), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A1").Select


'used to skip code if there are no transactions in month
skip1:


'Select macrobook & Clear Workings - BG trans & BG Trans raw tabs to loop through all Funds
    Windows(vFile).Activate
    Sheets("Workings - BG trans").Select
    Cells.Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    Sheets("BG Transactions raw").Select
    Cells.Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    Sheets("Pivot table 2").Delete

row = row + 1

Loop Until row = NumFunds

    Windows(vFile).Activate
    
'Turn pop-ups back on
    Application.DisplayAlerts = True

End Sub

 

[cooper645]

you could try adding

Application.ScreenUpdating = False 
Application.Calculation = xlManual

at the the beginning and

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

at the end

 

[offthelip]

I am not very surprised that you code is causing problems, you are doing a lot selects of entire columns, copy and pasting all in a loop while opening and closing files. EXCEL is not very good at managing memory so it is quite possible that it is running of memory.
If you have been following the posts on this forum you might have learnt that using selects and moving the active cell can be very hungry on resources.
Another possibility is a timing issue , you are writing out equations into a cell and then immediately copying the cells all down the worksheet and then sorting them. It is quite possible that the VBA is executing the next statement before excel has finished the recalculation, this could cause problems of any variety. What i have done to overcome timing issues is to force a complete recalculation at points in the VBA where timing was an issue by putting this code in:

Worksheets("Input data").EnableCalculation = False
Worksheets("Input data").EnableCalculation = True

this is for the worksheet "Input data"

The suggestion from cooper645 is a sensible suggestion but you will need to do some recalculatons in the middle, such as where you write the equations out, so you will need to turn calculations on and off again there.
I suspect that to really solve your problem you need to start again, and avoid using selects at all and learn how to write code that is not so resource hungry.
Do you really need to sort the data and is it alway 50000 rows of data ? Is it just to put the pivot table in.
why not do it all in VBA and get rid of the sort and the pivot table. Note I never use pivot tables and very rarely use VLOOKUP I always find it easier to write a bit vba to do the same thing

 

[JWatson220]

Thanks! I have add these to my script

 

[JWatson220]

Thanks @offthelip! I added clear clipboard and manual calculation commands to the script, that and adding pauses has resolved the issue.
Also I have added Lastrow commands for most row references to avoid an arbitrary high number.