Results 1 to 6 of 6

VBA Save Code Intermittment Hanging

Threaded View

  1. #1
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    VBA Save Code Intermittment Hanging

    Hi, I wonder whether someone may be able to help me please.

    I'm using the code below to perform the following:

    • Loop through a specifc set of sheets in my workbook,
    • Copy them,
    • Then create a new workbook, pasting the saved sheet into this, then
    • Save the file into a specific folder.

    Sub CopySheets()
        
        Dim SaveToDirectory As String
        Dim sh As Worksheet
        
        Application.ScreenUpdating = False
        
        On Error Resume Next
        Kill "\\C\s\CAF1\File Path\File Path\File Path\Extracted Managers Lists\*WA -*.xlsx"
        Kill "\\C\s\CAF1\File Path\File Path\File Path\Extracted Managers Lists\*SR -*.xlsx"
        On Error GoTo 0
        
        SaveToDirectory = "\\C\s\CAF1\File Path\File Path\File Path\Extracted Managers Lists"
        
        For Each sh In ActiveWorkbook.Worksheets
            Select Case sh.Name
                Case "Launch Sheet", "Email Recipients", "All Data", "All Resources", "Resources List", "Portfolio List", "IDEAS Forecast", "IDEAS Actuals", "Unique Records WA", "Unique Records SR", "CTO exc. C&I - Work Allocation", "CTO exc. C&I - Spare Resource"
                Case Else
                sh.Copy
                With ActiveWorkbook
                    .SaveAs Filename:=SaveToDirectory & "\" & sh.Name & ".xlsx"
                    .Close SaveChanges:=True
                End With
            End Select
        Next sh
    End Sub
    The code works, but every so often the code hangs with a pop up box saying that it is saving the file, highlighting the correct location with the only option of removing the pop uop box via a 'Cancel' button.

    When I do press 'Cancel' the code moves onto the next sheet, but does save the file, but it's a little time consuming to continue having to use the 'Cancel' button.

    I didn't have a problem with this when running the code in a Windows XP and Excel 2003 environment, but I've now moved to windows 7 and Excel 2013.

    I just wondered whether someone may be able to look at this please and offer some guidance on how I may overcome this.

    Many thanks and kind regards

    Chris
    Last edited by hobbiton73; 10-07-2014 at 07:05 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Win 7 Office 2010 save as hanging
    By nathansav in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-26-2014, 04:56 AM
  2. Code, that disables save, save as and close commands
    By mgmetev in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-02-2009, 06:31 PM
  3. Line of code hanging up on one machine???
    By Don G - ExcelForums.com in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-21-2005, 06:06 PM
  4. charting code hanging up
    By Papa Jonah in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-03-2005, 01:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1