Solved: Move excel files to a difference folder if sheet name is not "Payment Request"

[aravindhan_3]

Hi,

I have some 50 excel files in a folder, I open each file manually and see the sheet name if the sheet name is not "Payment Request" then i will move that file to a new folder in D:/temp.

is it possible in VBA to check all the files if the sheet name is Payment Request if not move those files to a folder.

cheers
Arvind

[Bob Phillips]

Sub LoopFiles()
Const FOLDER_SOURCE As String = "C:\Test\" '<<<< Change to suit
Const FOLDER_DEST As String = "C:\Target\" '<<<< Change to suit
Dim Filename As String

On Error Resume Next
MkDir FOLDER_DEST
On Error GoTo 0

Filename = Dir(FOLDER_SOURCE & "*.xls")
Do While Filename <> ""

Workbooks.Open FOLDER_SOURCE & Filename
If SheetExists("Payment Request", ActiveWorkbook) Then

ActiveWorkbook.Close savechanges:=False
Name FOLDER_SOURCE & Filename As FOLDER_DEST & Filename
Else

ActiveWorkbook.Close savechanges:=False
End If

Filename = Dir
Loop
End Sub

'-----------------------------------------------------------------
Function SheetExists(Sh As String, _
Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing)
On Error GoTo 0
End Function

[aravindhan_3]

Hi,

Its perfect and working

Thank you so much

Arvind

[Omer]

hi,
will this code work for .pdf files and if yes where do I save the code. I am new to vba :-). Thanks

[Dianne]

Is there a way to have a bit of code or a macro in Excel to export one sheet only, with formatting, to a new/blank database, and then save that new sheet as a specified string to include the name of the sheet it originated from?