Sub Подборка()

    Dim I As Long, j As Long, a(), b()
    Application.ScreenUpdating = False
    a = Sheets("Лимон").UsedRange.Value ': Cells.Clear
    With Sheets("Яблоко")
    For I = 2 To UBound(a, 2) ' с какого столбика копировать "i = 4"
        j = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        b = Application.Index(a, 0, I)
        .Cells(j, 1).Resize(UBound(b)).Value = b
    Next
    .Columns(1).SpecialCells(4).Delete xlUp
    End With
    
End Sub

=ОСТАТ(2*10;0,4*10)

Sub xx()
    With Application
        .ScreenUpdating = 0: .EnableEvents = 0: .DisplayAlerts = False
        Open ActiveWorkbook.Path & "\1.txt" For Input As #1
        With GetObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
            .SetText CreateObject("OlePrn.OleCvt.1").ToUnicode(Input$(LOF(1), 1), 65001)
            .PutInClipboard
        End With
        Close #1
        With [A2]
            Range(.Cells, .End(xlDown)).ClearContents
            .Cells(1).PasteSpecial xlPasteAll
            .Copy
        End With
        .CutCopyMode = 0
        [A1].Select
        .ScreenUpdating = 1: .EnableEvents = 1: .DisplayAlerts = 1
    End With
End Sub

Sub tt()
Dim c
For Each c In Selection
    c.Value = "'" & Trim(Replace(c.Value, " 1 шт.", ""))
Next
End Sub

=ИНДЕКС($D$2:$D$31;ПОИСКПОЗ(1;($C$2:$C$31=G2)*($B$2:$B$31>=F2)*($A$2:$A$31<=F2);0))

=СУММПРОИЗВ($C$3:$C$15*
($B$3:$B$15="г. Ржев")*
((
($D$3:$D$15>=45658)
+
($D$3:$D$15="")
)>0
))

=--ПРАВСИМВ(A4;19)

=ПРОСМОТРX(B2;$B$2:$B$15;$A$2:$A$15;;0)