Paste

PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, 0, 0

PasteSpecial xlPasteFormulas, xlPasteSpecialOperationNone, 0, 0

Target.Copy [B:C].Cells(n, 1)

Application.ScreenUpdating = 0

Private Sub Worksheet_Change(ByVal Target As Range)
     Dim n As Integer
     n = Application.CountA([C:C]) + 1
     If Not Intersect(Target, [A1]) Is Nothing And _
       Not (IsEmpty(Target)) And Target = [B1] Then
         Target.Copy [C:C].Cells(n, 1)
     End If
End Sub

With Worksheets("Лист4").Range("H6:H" & j, "S6:S" & j) 'ищем в этих диапозонах
     Set result = .Find(What:=Worksheets("Лист3").Range("d2").Offset(r, 0).Value, LookIn:=xlValues)  'это хочу найти
     If Not result Is Nothing Then
         firstAddress = result.Address 'запоминаем адрес первой найденной ячейки
         Do
             If Worksheets("Лист3").Range("c2").Offset(r, 0).Value = result.Offset(0, 22).Value Then  'сравнение
                 'Обрабатываем найденную ячейку
                 Worksheets("Лист3").Range("g2").Offset(r, 0).Value = result.Offset(0, -6).Value 'client
             End If
             Set result = .FindNext(result) 'ищем следующую
         Loop While Not result Is Nothing And result.Address <> firstAddress 'выход из цикла при переходе снова на первую
     End If
End With

=ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;"*";""))

=СУММ(--(ПСТР(A1;СТОЛБЕЦ(1:1);1)="*"))

=F1*СУММ((F1>={0;0.15;0.25;0.5;0.75;1;1.5;3;4;6;8;12;18;24;30;36;45;52.5;60;70;80;100;120;140;160;180;200;220})*(F1<{0.15;0.25;0.5;0.75;1;1.5;3;4;6;8;12;18;24;30;36;45;52.5;60;70;80;100;120;140;160;180;200;220;" "})*{33.75;29.25;27.3;20.22;16.65;12.69;11.88;10.98;8.77;7.07;6.15;4.76;4.13;3.52;3.06;2.62;2.33;2.01;1.68;1.56;1.22;1.04;0.9;0.8;0.73;0.66;0.61;0.58})/100*3.731

=F1*СУММ((F1>=min)*(F1<max)*Ki)/100*3.731

=СМЕЩ(ИНДЕКС(B:B;ПОИСКПОЗ(C1;A:A;0));0;0;СЧЁТЕСЛИ(A:A;C1))

Private Sub CommandButton1_Click()
Application.ScreenUpdating = 0: Application.EnableEvents = 0
Dim Rng, ResRng As Range
     If CommandButton1.Caption = "Скрыть пустые столбцы" Then
         Set Rng = Range("F5:J5").Find(Empty, LookIn:=xlValues)
         If Not Rng Is Nothing Then
             Set ResRng = Rng
             firstAddress = Rng.Address
             Do
                 Set Rng = Range("F5:J5").FindNext(Rng)
                 Set ResRng = Union(ResRng, Rng)
             Loop While Not Rng Is Nothing And Rng.Address <> firstAddress
             ResRng.EntireColumn.Hidden = 1
         End If
         Set Rng = Range("L6:L19").Find(Empty, LookIn:=xlValues)
         If Not Rng Is Nothing Then
             Set ResRng = Rng
             firstAddress = Rng.Address
             Do
                 Set Rng = Range("L6:L19").FindNext(Rng)
                 Set ResRng = Union(ResRng, Rng)
             Loop While Not Rng Is Nothing And Rng.Address <> firstAddress
             ResRng.EntireRow.Hidden = 1
         End If
         CommandButton1.Caption = "Отразить все столбцы"
     Else
         Range("F5:J5").Columns.Hidden = False
         Range("L6:L19").Rows.Hidden = False
         CommandButton1.Caption = "Скрыть пустые столбцы"
     End If
Application.ScreenUpdating = 1: Application.EnableEvents = 1
End Sub

{=ЕСЛИОШИБКА(ИНДЕКС(Список;СТРОКА()-1;{1;2;3});"")}

СМЕЩ(Лист1!C3;;;СЧЁТЗ(Лист1!C:C)-1)

=СУММ(ЕСЛИ(ЕОШИБКА(ПРОСМОТР(диапазон;значение));1))+1

=ПОИСКПОЗ(значение;диапазон);0)

Sub PageBreak()
      Application.ScreenUpdating = 0: Application.EnableEvents = 0
      Dim F&, L&, I&, J&, K&, S$
        
      With ActiveSheet.UsedRange
          F = .Column
          L = .Column + .Columns.Count - 1
      End With
        
      Do While I < ActiveSheet.HPageBreaks.Count
          I = I + 1
          J = ActiveSheet.HPageBreaks(I).Location.Row
          For K = F To L
              If Cells(J, K).MergeCells And Cells(J - 1, K).MergeCells Then
                  If Cells(J, K).MergeArea.Address = Cells(J - 1, K).MergeArea.Address _
                  And S <> Cells(J, K).MergeArea.Address Then
                      S = Cells(J, K).MergeArea.Address
                      Set ActiveSheet.HPageBreaks(I).Location = Cells(J, K).MergeArea.Offset(1)
                  End If
              End If
          Next
      Loop
      Application.ScreenUpdating = 1: Application.EnableEvents = 1
End Sub