Option Explicit
Sub Sbor()
Dim Sht As Worksheet
Dim i As Long
Dim iLastRow As Long
Dim iLR As Long
Dim ColArtikul As Integer
Dim ColNaimenov As Integer
Dim ColTsena As Integer
Dim ColZakaz As Integer
Dim Nomer As Integer
     Nomer = 1
     iLastRow = Cells(Rows.Count, 1).End(xlUp).Row
     If iLastRow > 7 Then
         Range("A7:E" & iLastRow).ClearContents
     End If
For Each Sht In Worksheets
       If Sht.Name <> "Заказ" Then        ' кроме листа "Заказ"
         With Sht
           iLastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            iLR = .Cells(.Rows.Count, 1).End(xlUp).Row
               ColZakaz = .Rows(1).Find("Заказ", , xlValues, xlWhole).Column
               ColTsena = .Rows(1).Find("Цена", , xlValues, xlWhole).Column
               ColNaimenov = .Rows(1).Find("Наименование", , xlValues, xlWhole).Column
               ColArtikul = .Rows(1).Find("Артикул", , xlValues, xlWhole).Column
             For i = 2 To iLR
               If Not IsEmpty(.Cells(i, ColZakaz)) Then
                 Cells(iLastRow, 1) = Nomer
                 Cells(iLastRow, 2) = .Cells(i, ColArtikul)
                 Cells(iLastRow, 3) = .Cells(i, ColNaimenov)
                 Cells(iLastRow, 4) = .Cells(i, ColZakaz)
                 Cells(iLastRow, 5) = .Cells(i, ColTsena)
                 iLastRow = iLastRow + 1
                 Nomer = Nomer + 1
               End If
             Next
         End With
       End If
     Next
End Sub

         Cells(iLastRow, 5) = .Cells(i, ColTsena) * .Cells(i, ColZakaz)