Hi all, i have a large spreadsheet which contains all my active, invoice sent, paid and cancelled invoices (called "JOBS"), i also have another sheet for an overview (called "INVOICES"), and finally a third sheet which is for archiving the job sheets (called "ArchiveJobs"). My macro is working well moving things around but it is painfully slow and i know there is some tricks i'm missing. I've been advised to get rid of selecting items but i can't figure out the code on certain lines to replace it with. I also know that turning screen updating off will help but i'm worried if it crashed on something that i won't see what the error is or if i need to exit early i won't know when to break the code.
Any help would be much appreciated.
Below is my working code which archives the jobs to a separate sheet and also moves folders in my dropbox that are related to the current invoice in to an archived folder inside dropbox.
I'm afraid i am not allowed to attach the workbook.
Sub ClearPaidInvoicesNewWay()
'default invoices view
Sheets("INVOICES").Select
Application.Run "'Trades.xlsm'!Invoices_Sheet_To_Default_View"
'filter out jobs we don't want
ActiveSheet.Range("$A$4:$W$1000").AutoFilter Field:=2, Criteria1:=Array( _
"CANCELED", "PAID", "Renumbered"), Operator:=xlFilterValues
Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(3,R[5]C[1]:R[9699]C[1])"
loopTimes = Range("A1").Value
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
'loop adjust how many times you want to run below
For x = 0 To loopTimes Step 1 'need to adjust to variable of how many jobs actually there as opposed to manual adjustment
'(x = 1 but i changed to 0) 8/12/15)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
'default invoices view
Sheets("INVOICES").Select
Application.Run "'Trades.xlsm'!Invoices_Sheet_To_Default_View"
'filter out jobs we don't want
ActiveSheet.Range("$A$4:$W$1000").AutoFilter Field:=2, Criteria1:=Array( _
"CANCELED", "PAID", "Renumbered"), Operator:=xlFilterValues
'find last
Range("C65536").Select
ActiveCell.End(xlUp).Select
'hyperlink to the actual invoice on jobs sheet
Application.Run "'Trades.xlsm'!findActivecellOnJobs"
'=====================================================================================
'bodge of a fix to move folder with pictures to archive
ActiveCell.Offset(0, -2).Select
Move_Job_Folder_To_Archive_Folder 'call?
'=====================================================================================
'select and cut invoice including blank top line
ActiveCell.Offset(-1, 0).Rows("1:55").EntireRow.Select
Selection.Copy
'select ArchiveJobs and paste invoice
'go to first blank
Sheets("ArchiveJobs").Select
Rows.Hidden = False
Range("G65536").Select
ActiveCell.End(xlUp).Offset(1, 0).Select
Range("A" & ActiveCell.Row).Select
ActiveSheet.Paste
'delete invoice from jobs sheet
Sheets("JOBS").Select
Selection.Delete Shift:=xlUp
'delete invoice summary from invoices sheet
Sheets("INVOICES").Select
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Delete Shift:=xlUp
'run macro again until no jobs left
Next x
Range("A1").Value = ""
'msg box to say complete with timer?
MsgBox ("Invoice move complete!")
End Sub
Display More