Move Files Containing Two Sheetnames to Another Folder

[mcorley]

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

[westconn1]

There appears to be something wrong with the nested loop

this is not very descriptive

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

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