can anyone tell me how i can simplify this code i seem to take forever to run and it flickers
can anyone tell me how i can simplify this code i seem to take forever to run and it flickers
What code?
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
taking forever suggest that you are looping or selecting unnecessarily
flickering can be resolved 99% of the time withyoull need toApplication.ScreenUpdating = Falseto update the screen for the user.Application.ScreenUpdating = True
this is typically done at the end of the routine or at key points throughout.
posting the code or the file for us will help us to better resolve your issues.
- I HAVE NO IDEA WHAT I'M DOING
sorry this code
Sub copysave() 'copy invoice to a new workbook ActiveSheet.copy newfn = "j:\INVOICES BACKUP\ALL\" & Range("e4").Value & "." & Range("a10") & ".xlsx" ActiveWorkbook.SaveAs newfn, FileFormat:=xlOpenXMLWorkbook ActiveSheet.PrintOut ActiveWorkbook.Close 'copy invoice to a new workbook ActiveSheet.copy newfn = "C:\Users\roger\Documents\aaa\" & Range("e4").Value & "." & Range("a10") & ".xlsx" ActiveWorkbook.SaveAs newfn, FileFormat:=xlOpenXMLWorkbook ActiveWorkbook.Close Range("e4").Value = Range("e4").Value + 1 Range("a18:d30").ClearContents Range("E3").ClearContents Range("a10").ClearContents ActiveWorkbook.Close End Sub
Last edited by SamT; 11-14-2017 at 07:57 AM.
Please envelop the code in Code Tags !
Does this work for you?
I think the runtime of this should depend on your machines ability to open a new instance of excel and save the file.Sub copysave() Application.ScreenUpdating = False 'YOU SHOULD VERIFY THAT THE RANGES E4 AND A10 HAVE DATA 'AND THAT THE FILE NAME IS VALID 'NOT DOING SO IS ASKING FOR A RUNTIME ERROR 'COPY INVOICE TO NEW WORKBOOK AND SAVE ActiveSheet.Copy newfn = "j:\INVOICES BACKUP\ALL\" & Range("e4").Value & "." & Range("a10") & ".xlsx" ActiveWorkbook.SaveAs newfn, FileFormat:=xlOpenXMLWorkbook ActiveSheet.PrintOut ActiveWorkbook.Close 'COPY INVOICE TO A NEW WORKBOOK AND SAVE ActiveSheet.Copy newfn = "C:\Users\roger\Documents\aaa\" & Range("e4").Value & "." & Range("a10") & ".xlsx" ActiveWorkbook.SaveAs newfn, FileFormat:=xlOpenXMLWorkbook ActiveWorkbook.Close 'INCREASE COUNTER BY 1 Range("e4").Value = Range("e4").Value + 1 'CLEAR INPUT'S CONTENTS Range("a18:d30").ClearContents Range("E3").ClearContents Range("a10").ClearContents ActiveWorkbook.Close Application.ScreenUpdating = True End Sub
But I'm no Guru.
You could combine some of your lines to shorten execution but truthfully I don't think its going to make any notable difference.
- I HAVE NO IDEA WHAT I'M DOING
Sub M_tst() Application.ScreenUpdating = False c00=cells(4,5) & "_" & cells(10,1) & ".xlsx" ActiveSheet.Copy with ActiveWorkbook .SaveAs "j:\INVOICES BACKUP\ALL\" & c00, 51 .Savecopyas "C:\Users\roger\Documents\aaa\" & c00 .close 0 end with with ActiveSheet .PrintOut .cells(4,5) = .cells(4,5) + 1 .Range("a18:d30,E3,A10").ClearContents end with ActiveWorkbook.Close -1 End Sub