Sub Tchpok()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim wb As Workbook
Set wb = ThisWorkbook
Dim sales As Worksheet
Set sales = wb.Sheets("Продажи")
Dim dz As Worksheet
Set dz = wb.Sheets("ДЗ")
Dim pay As Worksheet
Set pay = wb.Sheets("Оплаты")
Dim i As Long, n As Long, z As Long
i = Application.WorksheetFunction.CountA(sales.Columns(2)) + 5
sales.Range(Cells(8, 1), Cells(i, 1)).FormulaR1C1 = "=IFERROR(INDEX(Оплаты!C1,MATCH(Продажи!RC3,Оплаты!C3,0)),"""")"
sales.Range(Cells(8, 1), Cells(i, 1)).Copy
sales.Range(Cells(8, 1), Cells(i, 1)).PasteSpecial xlPasteValues
sales.Range(Cells(8, 17), Cells(i, 17)).FormulaR1C1 = "=IFERROR(INDEX(Менеджер_для_премии,MATCH(RC[-9],Менеджер_в_отчетах,0)),"""")"
sales.Range(Cells(8, 17), Cells(i, 17)).Copy
sales.Range(Cells(8, 17), Cells(i, 17)).PasteSpecial xlPasteValues
n = Application.WorksheetFunction.CountA(dz.Columns(1)) + 50
dz.Range(Cells(8, 15), Cells(n, 15)).FormulaR1C1 = "=IFERROR(INDEX(Продажи!C17,MATCH(ДЗ!RC1,Продажи!C3,0)),"""")"
dz.Range(Cells(8, 15), Cells(n, 15)).Copy
dz.Range(Cells(8, 15), Cells(n, 15)).PasteSpecial xlPasteValues
dz.Range(Cells(8, 16), Cells(n, 16)).FormulaR1C1 = "=IFERROR(INDEX(Месяц_период,MATCH(RC3,Дата_ДЗ,0)),R[-1]C)"
dz.Range(Cells(8, 16), Cells(n, 16)).Copy
dz.Range(Cells(8, 16), Cells(n, 16)).PasteSpecial xlPasteValues
dz.Range(Cells(8, 17), Cells(n, 17)).FormulaR1C1 = "=IFERROR(INDEX(Продажи!C6,MATCH(ДЗ!RC1,Продажи!C3,0)),"""")"
dz.Range(Cells(8, 17), Cells(n, 17)).Copy
dz.Range(Cells(8, 17), Cells(n, 17)).PasteSpecial xlPasteValues
z = Application.WorksheetFunction.CountA(pay.Columns(2)) + 50
pay.Range(Cells(7, 17), Cells(z, 17)).FormulaR1C1 = "=IFERROR(INDEX(Месяц_период,MATCH(RC[1],Месяц_период,0)),R[-1]C)"
pay.Range(Cells(8, 17), Cells(z, 17)).Copy
pay.Range(Cells(8, 17), Cells(z, 17)).PasteSpecial xlPasteValues
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
|