Hi Richard
I have included the full code (see Below). The macro was set up a few weeks ago and was running fine until this morning. Nobody else works on my files, so there is no possibility of anyone tampering with my code.
I find it very strange that my code now comes up with this run time error 6 overflow. It would be appreciated if you can assist in any way so as to overcome this error
Regards
Howard
Sub Auto_Update()
Application.ScreenUpdating = False
Import_Data
Delete_rows
delete_Dates
Color_Data
Clear_Dup_Add_Dates
Ageing
Totals
End Sub
Sub Import_Data()
Application.DisplayAlerts = False
Columns("A:Q").ClearContents
Workbooks.OpenText Filename:="C:\Extract\NISSWIP", Origin:=437, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False, _
Space:=True, OTHER:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3 _
, 4), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, _
1)), TrailingMinusNumbers:=True
Columns("C:C").EntireColumn.AutoFit
Columns("C:C").Select
Selection.NumberFormat = "@"
Selection.NumberFormat = "dd/mm/yyyy"
Columns("A:A").ColumnWidth = 15.71
Columns("G:G").EntireColumn.AutoFit
Range("A1:G6").Select
Selection.ClearContents
Range("A1").Select
ActiveCell.FormulaR1C1 = "NISS WIP"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("A1:A2").Select
Selection.Font.Bold = True
Range("A10:G10").Select
Selection.ClearContents
Range("B8").Select
ActiveCell.FormulaR1C1 = "Type"
Range("B9").Select
Selection.ClearContents
Range("A8").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A8:G9").Select
Selection.Font.Bold = True
Range("A1").Select
Windows("NISSWIP.xls").Activate
Columns("A:L").Select
Selection.ClearContents
Range("A1").Select
Windows("NISSWIP").Activate
Columns("A:I").Select
Selection.Copy
Windows("NISSWIP.xls").Activate
ActiveSheet.Paste
Columns("A:I").EntireColumn.AutoFit
Windows("NISSWIP").Activate
ActiveWorkbook.Close
Range("A8").Select
ActiveCell.FormulaR1C1 = "REF"
Range("C8").Select
ActiveCell.FormulaR1C1 = "Date"
Range("C9").Select
Selection.ClearContents
Range("D8").Select
ActiveCell.FormulaR1C1 = "Stock No."
Range("D9").Select
Selection.ClearContents
Range("E8").Select
ActiveCell.FormulaR1C1 = "Amount"
Range("E9").Select
Selection.ClearContents
Range("F9").Select
Selection.ClearContents
Range("H8").Select
Selection.Font.Bold = True
Range("I8").Select
Selection.Font.Bold = True
Range("A1").Select
End Sub
Sub Delete_rows()
For MY_ROWS = Range("A65536").End(xlUp).Row To 1 Step -1
If Range("A" & MY_ROWS).Value = "SS160301" Then
Rows(MY_ROWS & ":" & MY_ROWS + 9).Delete
End If
If Range("B" & MY_ROWS).Value = "---" Then
Rows(MY_ROWS).Delete
End If
Next MY_ROWS
End Sub
Sub delete_Dates()
For i = 1 To Cells(Rows.Count, "B").End(xlUp).Row
If IsDate(Cells(i, "B").Value) Then Cells(i, "B").ClearContents
Next i
Finalrow = Cells(65536, 3).End(xlUp).Row
For i = 1 To Finalrow
If Right(Cells(i, 3).Value, 4) <= "2000" Then
Cells(i, 3).ClearContents
End If
Next
Finalrow = Cells(65536, 3).End(xlUp).Row
For i = 1 To Finalrow
If Cells(i, 3).Value = "12:00:00 AM" Then
Cells(i, 3).ClearContents
End If
Next i
Finalrow = Cells(65536, 3).End(xlUp).Row
For i = 1 To Finalrow
If Right(Cells(i, 3).Value, 4) > "2020" Then
Cells(i, 3).ClearContents
End If
Next i
End Sub
Sub Color_Data()
Range("B1").Select
ActiveCell.FormulaR1C1 = "=NOW()"
ActiveCell.NumberFormat = "dd/mm/yyy"
ActiveCell.Font.Bold = True
Columns("A:H").Interior.ColorIndex = xlNone
For MY_ROWS = 1 To Range("C65536").End(xlUp).Row
If Range("C" & MY_ROWS).Value < Range("B1").Value - 30 _
And IsDate(Range("C" & MY_ROWS).Value) Then
Range("A" & MY_ROWS).Resize(3, 7).Interior.ColorIndex = 15
End If
Next MY_ROWS
Columns("C:C").NumberFormat = "dd/mm/YYYY"
Columns("B:B").EntireColumn.AutoFit
End Sub
Sub Ageing()
Range("J8").Select
ActiveCell.Value = "Age"
Dim lr As Long
lr = Cells(Rows.Count, "G").End(xlUp).Row
Range("J10:J" & lr).FormulaR1C1 = "=IF(RC[-7]="""","""",IF(RC[-3]="""","""",R1C2-RC[-7]))"
Columns("J:J").NumberFormat = "#,##0"
Finalrow = Range("A65536").End(xlUp).Row
Range("A" & Rows.Count).End(xlUp).Offset(-6).Resize(9, 9).Clear
End Sub
Sub Clear_Dup_Add_Dates()
Dim lr As Long, i As Long
lr = Range("g" & Rows.Count).End(xlUp).Row
For i = 12 To lr
If Not IsEmpty(Cells(i, "g")) And Not IsEmpty(Cells(i + 1, "g")) Then
If Cells(i, "g").Value = Cells(i + 1, "g").Value Then
Cells(i + 1, "g").ClearContents
ElseIf Cells(i, "g").Value <> Cells(i + 1, "g").Value Then
Cells(i + 1, "c").Value = Cells(i, "c").Value
End If
End If
Next
End Sub
Sub Totals()
Finalrow = Range("A65536").End(xlUp).Row
Range("A" & Finalrow + 6).Value = "No. of Job Cards"
Range("B" & Finalrow + 6).Formula = "=counta(A9:A" & Finalrow & ")"
Range("A" & Finalrow + 8).Value = "Total Values"
Range("E" & Finalrow + 8).Resize(, 5).Formula = "=Sum(E9:E" & Finalrow & ")"
Range("E" & Finalrow + 8).Resize(, 5).NumberFormat = "#,##0.00;(#,##0.00)"
Range("A" & Finalrow + 10).Value = "Ageing 30 Days & Less"
Range("E" & Finalrow + 10).Formula = "=Sumif(J9:J" & Finalrow & ",""<=""&30,E9:E" & Finalrow & ")"
Range("A" & Finalrow + 12).Value = "Ageing 31 to 60 Days"
Range("E" & Finalrow + 12).Formula = "=Sumif(J9:J" & Finalrow & ",""<=""&60,E9:E" & Finalrow & ")-Sumif(J9:J" & Finalrow & ",""<=""&30,E9:E" & Finalrow & ")"
Range("A" & Finalrow + 14).Value = "Ageing 61 to 90 Days"
Range("E" & Finalrow + 14).Formula = "=Sumif(J9:J" & Finalrow & ",""<=""&90,E9:E" & Finalrow & ")-Sumif(J9:J" & Finalrow & ",""<=""&60,E9:E" & Finalrow & ")"
Range("A" & Finalrow + 16).Value = "Ageing > 90 Days"
Range("E" & Finalrow + 16).Formula = "=Sumif(J9:J" & Finalrow & ","">""&90,E9:E" & Finalrow & ")"
Range("A" & Finalrow + 18).Value = "Total ageing"
Range("E" & Finalrow + 18).FormulaR1C1 = "=SUM(R[-8]C:R[-2]C)"
Range("A" & Finalrow + 18).Offset(, 5).FormulaR1C1 = "=R[-10]C[-1]-RC[-1]"
Range("A" & Finalrow + 18).Offset(, 5).Copy
Range("A" & Finalrow + 18).Offset(, 5).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("E" & Finalrow + 12).Formula = "=Sumif(J9:J" & Finalrow & ",""<=""&60,E9:E" & Finalrow & ")-Sumif(J9:J" & Finalrow & ",""<=""&30,E9:E" & Finalrow & ")+F" & Finalrow + 18 & ""
Range("A" & Finalrow + 16).Offset(, 4).NumberFormat = "General"
Range("A" & Finalrow + 18).Offset(, 4).NumberFormat = "#,##0.00;(#,##.00)"
Range("A" & Finalrow + 8).Offset(, 4).Resize(20).NumberFormat = "#,##0.00;(#,##.00)"
Range("A" & Finalrow + 18).Offset(, 5).NumberFormat = ";;;"
End Sub