Macro, that worked fine, not working on Excel 2016

[John T]

Hi, my work PC has just been updated from Excel 2010 to 2016.
I had a macro that worked fine before the upgrade but now when i run the macro from a button in 2016 it formats the worksheet incorrectly.
However if i go into Visual Basic and run the macro step by step it formats correctly.

Any ideas why this could be and how i can fix it?

 

[RoryA]

Hard to comment without seeing it. Can you post the code and clarify in what way it fails if you simply run it?

 

[John T]

Thanks for replying. The code is quite long as i've added and modified it over time.
I'm trying to format a bank statement.
It used to work fine on Excel 2010 but on Excel 2016 after the macro has run the data is in the wrong order the wrong cells have been deleated and pasted in the new sheet.
Hard to explain without showing you. If i then run the macro step by step however or even go in to the code and ctrl +F8 it at the end, it formats it fine.

Sub Macro3()'
' Macro3 Macro
'
Application.ScreenUpdating = False
If Range("a3") = "" Then


MsgBox "This sheet is not ready to be formatted", vbExclamation, "Error"
Exit Sub
 
Else


Call Macro1


End If


Range("A5").Select
    Selection.NumberFormat = "dd/mm/yyyy"
Range("F2").Formula = "=INDEX(Dates!C:C,MATCH(G5,Dates!A:A,0))"
Call Macro12
Call Macro13
Call Macro5
Call Macro22
Sheets("Barclays Statement").Select
Range("A1").Select
Application.ScreenUpdating = True


End Sub

Sub Macro1()
'
' Macro1 Macro
'


'


    Rows("2:7").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A20:A21").Select
    Selection.Cut
    Range("B1").Select
    ActiveSheet.Paste
    Range("B20:B21").Select
    Selection.Copy
    Range("D1").Select
    ActiveSheet.Paste
    Rows("3:28").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Columns("A:G").Select
    Columns("A:G").EntireColumn.AutoFit
    Range("A1").Select
    Call Macro2
    Call Macro4
    Call Macro8
    Call Macro9
    


End Sub

Sub Macro2()
'
' Macro2 Macro
'


'
    Cells.Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("A1").Select
End Sub

Sub Macro4()
'
' Macro4 Macro
'


'
    Range("A1:A2").Select
    Selection.Delete Shift:=xlToLeft
    Range("A500").Select
    Selection.End(xlUp).Select
    Selection.ClearContents
    Range("A1:B1").Select
    
    Range("A7:F7").Select
        With Selection.Font
        .Name = "Arial"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .Color = -16777216
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("A1").Select
End Sub

Sub Macro8()
'
' Macro8 Macro
'


'
    Range("A1:B1").Select
    With Selection
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Range("C1:D1").Select
    With Selection
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Range("C2:E2").Select
    With Selection
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Range("A1:B1").Select
End Sub

Sub Macro9()
'
' Macro9 Macro
'


'
    Columns("A:A").EntireColumn.AutoFit
    Columns("B:B").ColumnWidth = 29.11
    Columns("C:C").ColumnWidth = 10#
    Cells.Select
    With Selection
        .VerticalAlignment = xlTop
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
    End With
    Range("A1:B1").Select
    
End Sub


Sub Macro12()
'
' Macro12 Macro
'


'
    Range("G5").Select
    ActiveCell.FormulaR1C1 = "=+RC[-6]+0"
    Range("F2").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Suffix"
    Range("F1").Select
    With Selection.Font
        .Name = "Arial"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Selection.Font.Bold = True
    Range("F1").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlTop
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A1:B1").Select
End Sub
Sub Macro13()
'
' Macro13 Macro
'


'
    Range("A1:B1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Sheets.Add Before:=Sheets("Barclays Statement")
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    Range("A1:B1").Select
    Range("G5").Select
    Selection.ClearContents
    ActiveCell.Formula = "=VLOOKUP(F2,Dates!C:D,2,0)"
   
        Range("G5").Select
    ActiveSheet.Name = ActiveCell.Value
    Selection.ClearContents
    Range("G5").Select
    Selection.ClearContents
    Columns("G:G").Select
    With Selection.Font
        .Name = "Wingdings 2"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Call Macro19
    Call Macro16
    
    Range("A1:B1").Select
    
    Sheets("Barclays Statement").Select
    Range("G5").Select
    Selection.ClearContents
    Application.Goto Reference:="R1C1"
    


End Sub

Sub Macro19()
'
' Macro19 Macro
'


'
    Range("H5").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""O"",""hiphoray"")"
    Selection.AutoFill Destination:=Range("H5:H200"), Type:=xlFillDefault
    Range("H5:H200").Select
    Columns("H:H").Select
        With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    
    Range("A1:B1").Select
End Sub

Sub Macro16()
'
' Macro16 Macro
'


'
        Range("D5:D150").Select
    
    With Selection.FormatConditions.Add(xlExpression, Formula1:="=AND(D5<>0,G5=""P"")")
        .Interior.Color = RGB(153, 204, 0)
    End With
    
    Range("E5:E150").Select
    With Selection.FormatConditions.Add(xlExpression, Formula1:="=AND(E5<>0,G5=""P"")")
        .Interior.Color = RGB(153, 204, 0)
    End With
    
    Range("D5:D150").Select
    
    With Selection.FormatConditions.Add(xlExpression, Formula1:="=AND(D5<>0,G5=""O"")")
        .Interior.Color = RGB(255, 255, 0)
    End With
    
    Range("E5:E150").Select
    With Selection.FormatConditions.Add(xlExpression, Formula1:="=AND(E5<>0,G5=""O"")")
        .Interior.Color = RGB(255, 255, 0)
    End With
    
    
End Sub

Sub Macro5()
'
' Macro5 Macro
'


'
Application.ScreenUpdating = False
    Cells.Select
    Selection.ClearContents
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    Call Macro7
Application.ScreenUpdating = True


End Sub

Sub Macro7()
'
' Macro7 Macro
'


'
    ActiveCell.FormulaR1C1 = "PASTE STATEMENT HERE"
    Range("A1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A1:C1").Select
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A1").Select
    Selection.Font.Bold = True
    Range("A4").Select
End Sub


Sub Macro22()
'
' Macro22 Macro
'


'
Application.ScreenUpdating = False
    Sheets("Outstanding").Select
    Cells.Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    Call CopyToOutstanding
Call Macro20


    
    Application.ScreenUpdating = True
End Sub












Sub Macro20()
'
' Macro20 Macro
'


'
 
 Application.ScreenUpdating = False
    Sheets("Outstanding").Select
    Columns("H").Select
    Selection.ClearContents
    
    Columns("A:F").Select
    Columns("A:F").EntireColumn.AutoFit
    Columns("F").Select
    Selection.ClearContents
    Range("A1").Select
    Application.ScreenUpdating = True
End Sub

Sub CopyToOutstanding()


Application.ScreenUpdating = False
Dim Ws As Worksheet, Rng As Range




For Each Ws In ThisWorkbook.Worksheets
  If Ws.Name <> "Outstanding" Then
    With Ws
      For Each Rng In .Range(.Cells(2, "H"), (.Cells(Rows.Count, "H").End(xlUp)))
        If InStr(Rng, "hiphoray") Then Rng.EntireRow.Copy Sheets("Outstanding").Range("H" & Rows.Count).End(xlUp).Offset(1, -7)
      Next Rng
    End With
  End If
Next Ws
    Application.ScreenUpdating = True
End Sub