vba help

[rjw29bs4]

can anyone tell me how i can simplify this code i seem to take forever to run and it flickers

[SamT]

What code?

[MINCUS1308]

What code?

you cant see it SamT? lol

[MINCUS1308]

taking forever suggest that you are looping or selecting unnecessarily

flickering can be resolved 99% of the time with
Application.ScreenUpdating = False
youll need to
Application.ScreenUpdating = True to update the screen for the user.

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.

[rjw29bs4]

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

[snb]

Please envelop the code in Code Tags !

[MINCUS1308]

Does this work for you?

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


I think the runtime of this should depend on your machines ability to open a new instance of excel and save the file.
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.

[snb]

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