Consulting

Results 1 to 8 of 8

Thread: vba help

  1. #1

    vba help

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

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,147
    Location
    What code?
    Please take the time to read the Forum FAQ

  3. #3
    Quote Originally Posted by SamT View Post
    What code?
    you cant see it SamT? lol
    -I HAVE NO IDEA WHAT I'M DOING

  4. #4
    taking forever suggest that you are looping or selecting unnecessarily

    flickering can be resolved 99% of the time with
    Application.ScreenUpdating = False 
    
    
    Formatting tags added by mark007
    youll need to
    Application.ScreenUpdating = True 
    
    
    Formatting tags added by mark007
    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.
    -I HAVE NO IDEA WHAT I'M DOING

  5. #5
    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 
    
    
    Formatting tags added by mark007
    Last edited by SamT; 11-14-2017 at 07:57 AM.

  6. #6
    Please envelop the code in Code Tags !

  7. #7
    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 
    
    
    Formatting tags added by mark007
    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.
    -I HAVE NO IDEA WHAT I'M DOING

  8. #8
    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 
    
    
    Formatting tags added by mark007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •