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,
- 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
Bookmarks