Move Files Containing Two Sheetnames to Another Folder

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

Quote:

---

There appears to be something wrong with the nested loop

---

this is not very descriptive

Quote:

---

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

---

to read the sheets in the workbook, you must first open the workbook, which means at some point you need to create and instance of excel, preferably once only, not within the loop, you would need to close the workbook before trying to copy or move it

the last line quoted is looking for one sheet with the words copy and nutritional in the sheet name, not separate sheets
you should try like

Code:

---

onefound = false
for each ws in workbook.sheets
  if lcase(ws.name) = "copy" or lcase(ws.name) = "nutritional" then
    if not onefound then
      onefound = true
      else
      ' move workbook
      exit for
    end if
  end if
next

---

not tested