Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if folder exists, if yes just copy sheet in to folder?
Hi all, I have the code below that copies all visible WorkSheets to a new folder and renames the sheets, however i am struggling with the fact that if the folder exists the code stops and shows a Path error, how can i modify the code to check if folder exists, if it does just copy the worksheet with the DateString in to that folder? All help greatly appreciated! Regards, Simon Sub Copy_All_Visible_Sheets_To_New_Workbook() Dim WbMain As Workbook Dim Wb As Workbook Dim sh As Worksheet Dim DateString As String Dim FolderName As String Ash = ActiveSheet.Name Application.ScreenUpdating = False Application.EnableEvents = False DateString = Format(Now, "dd-mm-yyyy") Set WbMain = ThisWorkbook FolderName = WbMain.Path & "\" & Left(Ash, Len(Ash) - 0) MkDir FolderName For Each sh In WbMain.Worksheets If sh.Visible = -1 Then sh.Copy Set Wb = ActiveWorkbook Wb.SaveAs FolderName _ & "\" & Wb.Sheets(1).Name & " " & DateString & ".xls" Wb.Close False End If Next sh MsgBox "Look in " & FolderName & " for the files" Application.ScreenUpdating = True Application.EnableEvents = True End Sub -- Simon Lloyd ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708 View this thread: http://www.excelforum.com/showthread...hreadid=553148 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to check if a file exists in an ftp folder | Excel Discussion (Misc queries) | |||
how to check if folder exists | Excel Programming | |||
Copy folder and paste to another folder | Excel Programming | |||
How to copy 30 csv files from a folder to another folder | Excel Programming | |||
Create Folder / Copy Folder / Replace | Excel Programming |