Hi All,

I am attempting to move excel files from that contain both sheetnames ("Copy", and "Nutritional") from a target folder ("C:\Testing") to a destination folder ("C:\Sheetnames"). There appears to be something wrong with the nested loop and Access recognizing my Excel objects....Can anyone help with this??? Modification to the existing code would help the most or another example.



Private Sub _Click()

Dim Workbook As Excel
Dim ws As Worksheets
Dim FSO As Object
Dim FromPath As String
Dim ToPath As String
Dim Fdate As Date
Dim FileInFromFolder As Object

FromPath = "C:\Testing" '<< Change
ToPath = "C:\Sheetnames" '<< Change


If Right(FromPath, 1) <> "\" Then
FromPath = FromPath & "\"
End If

If Right(ToPath, 1) <> "\" Then
ToPath = ToPath & "\"
End If

Set FSO = CreateObject("scripting.filesystemobject")

If FSO.FolderExists(FromPath) = False Then
MsgBox FromPath & " doesn't exist"
Exit Sub
End If

If FSO.FolderExists(ToPath) = False Then
MsgBox ToPath & " doesn't exist"
Exit Sub
End If

For Each FileInFromFolder In FSO.GetFolder(FromPath).Files
For Each ws In Workbook.Sheets
If InStr(1, ws.Name, "Copy") And InStr(ws.Name, "Nutritional") Then
FileInFromFolder.Copy ToPath
End If
Next
Next FileInFromFolder

MsgBox "You can find the files from " & FromPath & " in " & ToPath
End Sub