собсно сам макрос
Sub простобланк()
Application.ScreenUpdating = False
Sheets("заявки на ЗЧ").Activate
Cells(Rows.Count, 2).End(xlUp).Select
ActiveCell.Offset(0, 0) = "марка"
ActiveCell.Offset(-1, 2) = Date
ActiveCell.Offset(-1, 5) = "=IF(ISERROR(OFFSET('[запись кузовного.xlsx]запись'!R1C15,MATCH(R[7]C[-3],'[запись кузовного.xlsx]запись'!R3C15:R2999C15,0),-7)),IF(ISERROR(OFFSET('[запись кузовного.xlsx]отданные'!R1C15,MATCH(R[7]C[-3],'[запись кузовного.xlsx]отданные'!R3C15:R2999C15,0),-7)),"""",""в отданных""),OFFSET('[запись кузовного.xlsx]запись'!R1C15,MATCH(R[7]C[-3],'[запись кузовного.xlsx]запись'!R3C15:R2999C15,0),-7))"
model = ActiveCell.Offset(0, 1).Address
marka = ActiveCell.Offset(-1, 1).Address
With ActiveCell.Offset(-1, 1).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=марка"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
ActiveCell.Offset(-1, 1) = "выбор_марки"
ActiveCell.Offset(0, 0) = "модэль"
Nom = ActiveCell.Offset(0, 1).Address
myReferenceStyle = Application.ReferenceStyle
Application.ReferenceStyle = xlR1C1
With ActiveCell.Offset(-1, 1).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=indirect(R[-1]C)"
.InCellDropdown = True
End With
Application.ReferenceStyle = myReferenceStyle
ActiveCell.Offset(5, 2) = "=" & model & "&" & Nom
ActiveCell.Offset(5, 2).Font.Italic = True
ActiveCell.Offset(0, 0) = "гос номер"
ActiveCell.Offset(0, 0) = "VIN"
ActiveCell.Offset(0, 0) = "год"
ActiveCell.Offset(0, 0) = "цвет"
ActiveCell.Offset(0, 0) = "страховая"
With ActiveCell.Offset(-1, 1).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=страховые"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = False
End With
ActiveCell.Offset(0, 1).NumberFormat = "@"
ActiveCell.Offset(0, 0) = "№заказ-наряда"
ActiveCell.Offset(0, 0) = "к-во деталей"
ActiveCell.Offset(0, 2) = "=IFERROR(OFFSET('[запись кузовного.xlsx]отданные'!R1C15,MATCH(R[-1]C,'[запись кузовного.xlsx]отданные'!R2C15:R2999C15,0),-7),"""")"
ActiveCell.Offset(-1, 1) = "=IF(SUM(R[1]C:R[4]C)=0,"""",SUM(R[1]C:R[4]C))"
ActiveCell.Offset(-1, 3) = "=IF(SUM(R[1]C:R[4]C)=0,"""",SUM(R[1]C:R[4]C))"
ActiveCell.Offset(1, 1) = "=IF(RC[-1]="""","""",1)"
ActiveCell.Offset(2, 1) = "=IF(RC[-1]="""","""",1)"
ActiveCell.Offset(0, 0) = "Реферанс"
ActiveCell.Offset(-1, -1) = "№"
ActiveCell.Offset(-1, 1) = "Кол-во"
ActiveCell.Offset(-1, 2) = "Наименоваеие"
ActiveCell.Offset(-1, 3) = "получено"
ActiveCell.Offset(-1, 4) = "Цена"
ActiveCell.Offset(-1, 5) = "на складе"
ActiveCell.Offset(-1, 6) = "в сервисных"
ActiveCell.Offset(-1, 7) = "в заявках"
ActiveCell.Offset(-1, 8) = "в заказах"
ActiveCell.Offset(-1, 9) = "заказано"
ActiveCell.Offset(0, -1) = "=IF(RC[1]="""","""",if(rc[1]=""дозаказ"",""№"",IF(R[-1]C=""№"",1,R[-1]C+1)))"
ActiveCell.Offset(0, 2) = "=IFERROR(IF(ISERROR(OFFSET(INDIRECT(""'прайс "" & RC[9]& ""'!R1C1"",FALSE),MATCH(RC[-2],INDIRECT(""'прайс "" & RC[9]& ""'!R2C1:R50000C1"",FALSE),0),2)),OFFSET(INDIRECT(""'прайс Рено'!R1C1"",FALSE),MATCH(RC[-2],INDIRECT(""'прайс рено'!R2C1:R50000C1"",FALSE),0),2),OFFSET(INDIRECT(""'прайс "" & RC[9]& ""'!R1C1"",FALSE),MATCH(RC[-2],INDIRECT(""'прайс "" & RC[9]& ""'!R2C1:R50000C1"",FALSE),0),2)),"""")"
ActiveCell.Offset(0, 4) = "=IFERROR(IF(ISERROR(OFFSET(INDIRECT(""'прайс "" & RC[7]& ""'!R1C1"",FALSE),MATCH(RC[-4],INDIRECT(""'прайс "" & RC[7]& ""'!R2C1:R50000C1"",FALSE),0),1)),OFFSET(INDIRECT(""'прайс Рено'!R1C1"",FALSE),MATCH(RC[-4],INDIRECT(""'прайс рено'!R2C1:R50000C1"",FALSE),0),1),OFFSET(INDIRECT(""'прайс "" & RC[7]& ""'!R1C1"",FALSE),MATCH(RC[-4],INDIRECT(""'прайс "" & RC[7]& ""'!R2C1:R50000C1"",FALSE),0),1)),"""")"
ActiveCell.Offset(0, 5) = "=iferror(OFFSET('прайс Рено'!R1C1,MATCH(RC[-5],'прайс Рено'!R2C1:R10000C1,0),6),"""")"
ActiveCell.Offset(0, 6) = "=iferror(OFFSET('прайс Рено'!R1C1,MATCH(RC[-6],'прайс Рено'!R2C1:R10000C1,0),7),"""")"
ActiveCell.Offset(0, 7) = "=SUMIF(C[-7],RC[-7],C[-6])"
ActiveCell.Offset(0, 8) = "=IFERROR(OFFSET(заказы!R1C1,MATCH(RC[-8],заказы!R2C1:R10000C1,0),2),"""")"
ActiveCell.Offset(1, -1) = "=IF(RC[1]="""","""",if(rc[1]=""дозаказ"",""№"",IF(R[-1]C=""№"",1,R[-1]C+1)))"
Range(ActiveCell.Offset(-1, -1), ActiveCell.Offset(-1, 9)).Borders.Weight = xlMedium
Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(1, 9)).Borders.LineStyle = xlContinuous
ActiveCell.Offset(2, 0) = "марка"
For x = 1 To 12
ActiveCell.Offset(x - 14, 10) = "=" & model & "&" & Nom
ActiveCell.Offset(x - 14, 11) = "=" & marka
Next
Application.ScreenUpdating = True
End Sub
работает он в свяске с
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("b:b")) Is Nothing Then
Target.Offset(1, 0).Select
Application.EnableEvents = False
If ActiveCell = "марка" Then
Selection.EntireRow.Insert
Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0, 9)).Borders.LineStyle = xlContinuous
Target.Offset(0, -1) = "=IF(RC[1]="""","""",if(rc[1]=""дозаказ"",""№"",IF(R[-1]C=""№"",1,R[-1]C+1)))"
Target.Offset(1, 1) = "=IF(RC[-1]="""","""",1)"
Target.Offset(0, 2) = "=IFERROR(IF(ISERROR(OFFSET(INDIRECT(""'прайс "" & RC[9]& ""'!R1C1"",FALSE),MATCH(RC[-2],INDIRECT(""'прайс "" & RC[9]& ""'!R2C1:R50000C1"",FALSE),0),2)),OFFSET(INDIRECT(""'прайс Рено'!R1C1"",FALSE),MATCH(RC[-2],INDIRECT(""'прайс рено'!R2C1:R50000C1"",FALSE),0),2),OFFSET(INDIRECT(""'прайс "" & RC[9]& ""'!R1C1"",FALSE),MATCH(RC[-2],INDIRECT(""'прайс "" & RC[9]& ""'!R2C1:R50000C1"",FALSE),0),2)),"""")"
Target.Offset(0, 4) = "=IFERROR(IF(ISERROR(OFFSET(INDIRECT(""'прайс "" & RC[7]& ""'!R1C1"",FALSE),MATCH(RC[-4],INDIRECT(""'прайс "" & RC[7]& ""'!R2C1:R50000C1"",FALSE),0),1)),OFFSET(INDIRECT(""'прайс Рено'!R1C1"",FALSE),MATCH(RC[-4],INDIRECT(""'прайс рено'!R2C1:R50000C1"",FALSE),0),1),OFFSET(INDIRECT(""'прайс "" & RC[7]& ""'!R1C1"",FALSE),MATCH(RC[-4],INDIRECT(""'прайс "" & RC[7]& ""'!R2C1:R50000C1"",FALSE),0),1)),"""")"
Target.Offset(0, 5) = "=iferror(OFFSET('прайс Рено'!R1C1,MATCH(RC[-5],'прайс Рено'!R2C1:R10000C1,0),6),"""")"
Target.Offset(0, 6) = "=iferror(OFFSET('прайс Рено'!R1C1,MATCH(RC[-6],'прайс Рено'!R2C1:R10000C1,0),7),"""")"
Target.Offset(0, 7) = "=SUMIF(C[-7],RC[-7],C[-6])"
Target.Offset(0, 8) = "=IFERROR(OFFSET(заказы!R1C1,MATCH(RC[-8],заказы!R2C1:R10000C1,0),2),"""")"
x = Target.Offset(-1, 10).Address
y = Target.Offset(-1, 11).Address
Target.Offset(1, 10) = "=" & x
Target.Offset(1, 11) = "=" & y
Target.Offset(1, -1) = "=IF(RC[1]="""","""",if(rc[1]=""дозаказ"",""№"",IF(R[-1]C=""№"",1,R[-1]C+1)))"
End If
If ActiveCell.Offset(-1, 0) = "дозаказ" Then
Range(ActiveCell.Offset(-1, 1), ActiveCell.Offset(-1, 9)) = ""
End If
End If
Application.EnableEvents = True
End Sub
