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,840
    Location
    What code?
    Please take the time to read the Forum FAQ

  3. #3
    VBAX Tutor MINCUS1308's Avatar
    Joined
    Jun 2014
    Location
    UNDER MY DESK
    Posts
    253
    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
    VBAX Tutor MINCUS1308's Avatar
    Joined
    Jun 2014
    Location
    UNDER MY DESK
    Posts
    253
    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.
    - 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
    Last edited by SamT; 11-14-2017 at 07:57 AM.

  6. #6
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,416
    Please envelop the code in Code Tags !

  7. #7
    VBAX Tutor MINCUS1308's Avatar
    Joined
    Jun 2014
    Location
    UNDER MY DESK
    Posts
    253
    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.
    - I HAVE NO IDEA WHAT I'M DOING

  8. #8
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,416
    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

Posting Permissions

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