Dim sheet As Worksheet
   Dim cell As Range
   s = 1
   With ActiveWorkbook
      For Each sheet In ActiveWorkbook.Worksheets
         If sheet.Name <> "Реестр" Then
         Set cell = Worksheets("Реестр").Cells(s + 2, 2)
         s = s + 1
         .Worksheets("Реестр").Hyperlinks.Add anchor:=cell, Address:="", SubAddress:="'" & sheet.Name & "'" & "!A1"
         cell.Formula = sheet.Name
         End If
      Next
   End With
End Sub

Sheets("Реестр").Select
    Rows("3:3").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "=R[1]C+1"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "='АВС'!R[1]C[-1]"

Sub Zakaz()
Dim Sht As Worksheet
Dim i As Long
Dim iLastRow As Long
Dim iLR As Long
Dim Naimenovanie As Range
Dim FoundZakaz As Range
Dim TSena As Range
  Range("A7:E33").ClearContents  'очищаем диапазон под данные
    For Each Sht In Worksheets
      If Sht.Name <> "заказ" Then
        With Sht
         Set FoundZakaz = .Rows("1:2").Find("заказ", , xlValues, xlWhole, 2)
          iLR = .Cells(.Rows.Count, FoundZakaz.Column).End(xlUp).Row
           Set TSena = .Rows("1:2").Find("Цена", , xlValues, xlWhole)
           Set Naimenovanie = .Rows("1:2").Find("Наименование", , xlValues, xlWhole, 2)
         For i = 3 To iLR
           If Not IsEmpty(.Cells(i, FoundZakaz.Column)) Then        'есть заказ
             iLastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
             If iLastRow < 34 Then
               Cells(iLastRow, 1) = .Cells(i, Naimenovanie.Column)  'наименование
               Cells(iLastRow, 2) = .Name                           'поставщик
               Cells(iLastRow, 3) = .Cells(i, FoundZakaz.Column)    'кол-во
               Cells(iLastRow, 5) = .Cells(i, TSena.Column)         'цена
             Else
               MsgBox "Закончились строки бланка заказа"
               Exit Sub
             End If
           End If
         Next
        End With
      End If
    Next
    For Each Sht In Worksheets
      If Sht.Name <> "Заказ" Then
With Sht
Set FoundZakaz = .Rows("1:2").Find("заказ", , xlValues, xlWhole, 2)
         .Columns(FoundZakaz.Column).ClearContents
         .Cells(1, FoundZakaz.Column) = "заказ"
        End With
        End If
     Next
End Sub