Hi All
I have a workbook that I need to copy one sheet from to a folder I'm creating as part of the process.
I'm not having problems with the folder creation, just copying the sheet to the correct folder
What I am trying to do is put sheet 1 into "1 PHJ Daybook"
Sheet 1 is "Daybook Import Sheet"
I'm obviously going wrong at the end, I've been going around in circles with this for hours now, any ideas where I am going wrong??
Kind regards
Paul
I have a workbook that I need to copy one sheet from to a folder I'm creating as part of the process.
I'm not having problems with the folder creation, just copying the sheet to the correct folder
What I am trying to do is put sheet 1 into "1 PHJ Daybook"
Sheet 1 is "Daybook Import Sheet"
I'm obviously going wrong at the end, I've been going around in circles with this for hours now, any ideas where I am going wrong??
Kind regards
Paul
VBA Code:
Sub make_folders()
Application.ScreenUpdating = False
Dim myPackFolder As String
Dim myFolder As String
Dim Mycustomer As String
Dim myFolders()
Dim myIndex As Integer
Dim myfilepath As String
ReDim myFolders(99)
myFolders(0) = "1 PHJ Daybook"
myFolders(1) = "2 PPS System Import File"
myFolders(2) = "3 Engineers Route Planners"
myFolders(3) = "4 PPS Web App Day Report"
myFolders(4) = "5 Additional Information"
myfilepath = ThisWorkbook.Path
myFolder = myfilepath & "\Daily Reports\"
Mycustomer = VBA.Format(VBA.Now, "dd-MMM-yyyy")
myPackFolder = myFolder & "\" & Mycustomer & " Excel Files"
'check if folders already exist
If Dir(myPackFolder, vbDirectory) = "" Then
MkDir myPackFolder
Else
MsgBox "This date alread exists." & vbCr & vbCr & "Please check and try again.", vbExclamation, "Folder Error"
Exit Sub
End If
For myIndex = 0 To UBound(myFolders)
If myFolders(myIndex) = "" Then
Exit For
End If
MkDir myPackFolder & "\" & Mycustomer & "-" & myFolders(myIndex)
Next myIndex
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sheet1.Activate
Worksheets("This Sheet").SaveAs ThisWorkbook.Path & "\Daily Reports\" & VBA.Format(VBA.Now, "dd-MMM-yyyy") & _
" Excel Files" & "\" & VBA.Format(VBA.Now, "dd-MMM-yyyy") & "-1 PHJ Daybook" & "\Daybook Import Sheet.xlsm"
MsgBox ("The record folders and PPS Import File have been created for: ") & Mycustomer, vbInformation + vbOKOnly, "Folder Created"
End Sub