Sub Rio_SheetName_Reformer()

'Author:    Roman Rioran Voronov
'Date:      the 11-th of September, 2014
'Feedback:  voronov_rv@mail.ru

'     This subroutine allows user to pick a folder, where in every Excel file
'all worksheets with symbol "_" in their names will be renamed

Dim X As Integer 'To roll list names
Dim Y As Integer 'To roll file names on page
Dim Z As Integer 'As "Podzakaz" number
Dim shtX As Worksheet 'To roll lists
Dim sFiles As String 'To roll files
Dim sFolder As String 'To select a folder
Dim FileNumber As Integer 'To roll files

With Application.FileDialog(msoFileDialogFolderPicker)
     If .Show = False Then Exit Sub
     sFolder = .SelectedItems(1)
End With

Application.ScreenUpdating = False: ThisWorkbook.Worksheets("Data").Range("A:A").Clear
sFolder = sFolder & IIf(Right(sFolder, 1) = Application.PathSeparator, "", Application.PathSeparator)
sFiles = Dir(sFolder & "*.xls*")
FileNumber = 0

Do 'Gathering file names
     FileNumber = FileNumber + 1
     With ThisWorkbook.Worksheets("Data")
         .Cells(FileNumber, 1).Value = sFolder & sFiles
         sFiles = Dir
     End With
Loop While sFiles <> "" 'Observing files one by one

For Y = 1 To FileNumber 'Reforming books
     Workbooks.Open ThisWorkbook.Worksheets("Data").Cells(Y, 1).Value: Z = 0
     For Each shtX In ActiveWorkbook.Worksheets
         If shtX.Name Like "*_*" Then
             Z = Z + 1: shtX.Name = "Подзаказ" & Z
         End If
     Next shtX
     ActiveWorkbook.Close SaveChanges:=True
Next Y

Application.ScreenUpdating = True
MsgBox "Обработка книг в папке" & vbNewLine & vbNewLine & sFolder & vbNewLine & vbNewLine & "полностью завершена."

End Sub

=[Книга_База.xls]DS_MT!A1

=[Книга_База.xls]#ССЫЛКА!A1