API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Mar 21st, 2017
99
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
VB.NET 87.98 KB | None | 0 0
raw download clone embed print report
  1. Dim Year As String
  2. Dim Year_prev As String
  3. Dim Month, Month_prev As String
  4. Dim Months_number As Integer
  5. Dim months_a(1 To 12) As String
  6. Dim cat_number_tv, adv_number_tv, cat_number_internet, adv_number_internet, cat_number_other, adv_number_other As Long
  7. Dim n As Integer
  8. Dim enter1, enter2 As Long
  9.  
  10. Sub Monthly_base1()
  11.  
  12. '''''''''''''''''''''''''''''''''''''''''''Ìàêðîñ äëÿ ëèñòà "Òåìïëåéò" - âèòÿæêà äëÿ ôîðìóâàííÿ ïðåçåíòàö³¿.
  13. '''''''''''''''''''''''''''''''''''''''''''
  14. '''''''''''''''''''''''''''''''''''''''''''
  15. 'Ââåñòè íîìåð ñòîâï÷èêà äëÿ ïîïåðåäíüîãî ì³ñÿöÿ òóò
  16. enter1 = 3
  17. enter2 = 2
  18. '''''''''''''''''''''''''''''''''''''''''''
  19. '''''''''''''''''''''''''''''''''''''''''''
  20. '''''''''''''''''''''''''''''''''''''''''''
  21.  
  22. 'clear
  23. Sheets(3).Select
  24.  
  25. Range(Cells(5, 3), Cells(1048576, 78)).Select
  26. Selection.ClearContents
  27.  
  28. Cells(1, 1).Select
  29.  
  30.  
  31.    
  32. 'Çàïîâíþºìî àðõ³â ç íàçâàìè ì³ñÿö³â
  33. months_a(1) = "Jan"
  34. months_a(2) = "Feb"
  35. months_a(3) = "Mar"
  36. months_a(4) = "Apr"
  37. months_a(5) = "May"
  38. months_a(6) = "Jun"
  39. months_a(7) = "Jul"
  40. months_a(8) = "Aug"
  41. months_a(9) = "Sep"
  42. months_a(10) = "Oct"
  43. months_a(11) = "Nov"
  44. months_a(12) = "Dec"
  45.  
  46.  
  47. 'Øóêàºìî ð³ê, ì³ñÿöü, ç ÿêèì ïðàöþºìî
  48.  
  49. 'гê
  50.  
  51. Sheets(1).Select
  52. With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  53.         "Year")
  54.         .Orientation = xlColumnField
  55.         .Position = 1
  56. End With
  57. Cells.Find(What:="Year", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
  58.         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
  59.         False, SearchFormat:=False).Activate
  60. ActiveCell.Offset(1, 0).Activate
  61. r = ActiveCell.Row
  62. c = ActiveCell.Column
  63. For i = 1 To 100
  64.    If Cells(r, c + i).Value = "" Then Exit For
  65. Next i
  66.  
  67. Year = Cells(r, c + i - 2).Value
  68. Year_prev = Cells(r, c + i - 3).Value
  69.  
  70. Year = 2016
  71. Year_prev = 2015
  72.  
  73. '̳ñÿöü
  74.  
  75.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Year") _
  76.         .Orientation = xlHidden
  77.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  78.         "Month")
  79.         .Orientation = xlColumnField
  80.         .Position = 1
  81.     End With
  82.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  83.         "Year")
  84.         .Orientation = xlPageField
  85.         .Position = 1
  86.     End With
  87.     On Error Resume Next
  88.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Year") _
  89.         .CurrentPage = Year
  90.         On Error Resume Next
  91.  
  92. If Cells(5, 4).Value = "" Then
  93.     Month = "Jan"
  94.     Month_prev = "Dec"
  95.     Months_number = 1
  96. ElseIf Cells(5, 5).Value = "" Then
  97.     Month = "Feb"
  98.     Month_prev = "Jan"
  99.     Months_number = 2
  100. ElseIf Cells(5, 6).Value = "" Then
  101.     Month = "Mar"
  102.     Month_prev = "Feb"
  103.     Months_number = 3
  104. ElseIf Cells(5, 7).Value = "" Then
  105.     Month = "Apr"
  106.     Month_prev = "Mar"
  107.     Months_number = 4
  108. ElseIf Cells(5, 8).Value = "" Then
  109.     Month = "May"
  110.     Month_prev = "Apr"
  111.     Months_number = 5
  112. ElseIf Cells(5, 9).Value = "" Then
  113.     Month = "Jun"
  114.     Month_prev = "May"
  115.     Months_number = 6
  116. ElseIf Cells(5, 10).Value = "" Then
  117.     Month = "Jul"
  118.     Month_prev = "Jun"
  119.     Months_number = 7
  120. ElseIf Cells(5, 11).Value = "" Then
  121.     Month = "Aug"
  122.     Month_prev = "Jul"
  123.     Months_number = 8
  124. ElseIf Cells(5, 12).Value = "" Then
  125.     Month = "Sep"
  126.     Month_prev = "Aug"
  127.     Months_number = 9
  128. ElseIf Cells(5, 13).Value = "" Then
  129.     Month = "Oct"
  130.     Month_prev = "Sep"
  131.     Months_number = 10
  132. ElseIf Cells(5, 14).Value = "" Then
  133.     Month = "Nov"
  134.     Month_prev = "Oct"
  135.     Months_number = 11
  136. ElseIf Cells(5, 15).Value = "" Then
  137.     Month = "Dec"
  138.     Month_prev = "Nov"
  139.     Months_number = 12
  140. End If
  141.  
  142.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Month" _
  143.         ).Orientation = xlHidden
  144.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Year") _
  145.         .CurrentPage = "(All)"
  146.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Year") _
  147.         .Orientation = xlHidden
  148.        
  149. Call TV_categories
  150. Call TV_advertisers
  151. 'Call Internet_categories("video")
  152. 'Call Internet_categories("banner")
  153. 'Call Internet_advertisers("video")
  154. 'Call Internet_advertisers("banner")
  155. Call Other_categories("Press")
  156. Call Other_advertisers("Press")
  157. Call Other_categories("Outdoor")
  158. Call Other_advertisers("Outdoor")
  159. Call Other_categories("Radio")
  160. Call Other_advertisers("Radio")
  161.  
  162.        
  163.    
  164. End Sub
  165. Sub TV_categories()
  166. '
  167. ''TV
  168.  
  169. 'Filters
  170.  
  171.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").AddDataField _
  172.         ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  173.         "18-50 50+ EqGRP%"), "Êîëè÷åñòâî ïî ïîëþ 18-50 50+ EqGRP%", xlCount
  174.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  175.         "Category_eng")
  176.         .Orientation = xlPageField
  177.         .Position = 1
  178.     End With
  179.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  180.         "media")
  181.         .Orientation = xlPageField
  182.         .Position = 1
  183.     End With
  184.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  185.         "Month")
  186.         .Orientation = xlPageField
  187.         .Position = 1
  188.     End With
  189.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  190.         "Year")
  191.         .Orientation = xlColumnField
  192.         .Position = 1
  193.     End With
  194.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  195.         "Spot_Type")
  196.         .Orientation = xlPageField
  197.         .Position = 2
  198.     End With
  199.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  200.         "Category_eng").CurrentPage = "(All)"
  201.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  202.         "Category_eng")
  203.         '.PivotItems("################").Visible = False
  204.         .PivotItems("Advertising").Visible = False
  205.         .PivotItems("Mass Media").Visible = False
  206.         .PivotItems("Social").Visible = False
  207.     End With
  208.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  209.         "Category_eng").EnableMultiplePageItems = True
  210.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  211.         "Category_eng")
  212.         .Orientation = xlRowField
  213.         .Position = 1
  214.     End With
  215.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  216.         ).CurrentPage = "(All)"
  217.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  218.         "media")
  219.         '.PivotItems("################").Visible = False
  220.         .PivotItems("Internet").Visible = False
  221.         .PivotItems("Outdoor").Visible = False
  222.         .PivotItems("Press").Visible = False
  223.         .PivotItems("Radio").Visible = False
  224.     End With
  225.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  226.         ).EnableMultiplePageItems = True
  227.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  228.         "Spot_Type").CurrentPage = "(All)"
  229.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  230.         "Spot_Type")
  231.         '.PivotItems("################").Visible = False
  232.         .PivotItems("banner").Visible = False
  233.         .PivotItems("video").Visible = False
  234.         .PivotItems("àâòîïðîìîóøí").Visible = False
  235.         .PivotItems("àâòîïðîìîóøí ÏÝ").Visible = False
  236.         .PivotItems("áåãóùàÿ ñòðîêà").Visible = False
  237.         .PivotItems("Áðåíäèíã").Visible = False
  238.         .PivotItems("Ãàçåòà").Visible = False
  239.         .PivotItems("ãîëîñîâàíèå ïî òåëåôîíó").Visible = False
  240.         .PivotItems("ãîëîñîâàíèå ïî òåëåôîíó ÏÝ").Visible = False
  241.         .PivotItems("Ãðàôè÷åñêàÿ ìîäåëü").Visible = False
  242.         .PivotItems("Æóðíàë").Visible = False
  243.         .PivotItems("Èíôîðìàöèîííûé ñþæåò").Visible = False
  244.         .PivotItems("Èñïîëüçîâàíèå ðåêâèçèòà").Visible = False
  245.         .PivotItems("êëèï ñîöèàëüíîé ðåêëàìû").Visible = False
  246.         .PivotItems("ëîãîòèï").Visible = False
  247.         .PivotItems("Ëîãîòèï+Äèêòîð").Visible = False
  248.         .PivotItems("Ïëàøêà").Visible = False
  249.         .PivotItems("Ñïîíñîðñêàÿ áåãóùàÿ ñòðîêà").Visible = False
  250.         .PivotItems("Ñïîíñîðñêàÿ áëàãîäàðíîñòü â òèòðàõ").Visible = False
  251.         .PivotItems("Ñïîíñîðñêàÿ äèíàìè÷åñêàÿ çàñòàâêà").Visible = False
  252.         .PivotItems("ñïîíñîðñêàÿ çàñòàâêà").Visible = False
  253.         .PivotItems("ñïîíñîðñêàÿ òèòð-çàñòàâêà").Visible = False
  254.         .PivotItems("Ñïîíñîðñêèé ðåêâèçèò").Visible = False
  255.         .PivotItems("Ñïîíñîðñêîå îáúÿâëåíèå").Visible = False
  256.         .PivotItems("Èíôîðìàöèîí=ûé ñþæåò").Visible = False
  257.         .PivotItems("ïðåññà ìàêåò").Visible = False
  258.         .PivotItems("ñïîíñîðñêèé êëèï").Visible = False
  259.     End With
  260.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  261.         "Spot_Type")
  262.         .PivotItems("òåëåïðîäàæà").Visible = False
  263.         .PivotItems("óïîìèíàíèå â òèòðàõ").Visible = False
  264.         .PivotItems("(blank)").Visible = False
  265.     End With
  266.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  267.         "Spot_Type").EnableMultiplePageItems = True
  268.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Month" _
  269.         ).CurrentPage = "(All)"
  270.        
  271.        
  272. 'Ïåð³îä äî ïåð³îäó ïîïåðåäíüîãî ðîêó
  273.  
  274.    For b = 2009 To Year - 2
  275.      
  276.    With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  277.         "Year")
  278.         .PivotItems(CStr(b)).Visible = False
  279.     End With
  280.    
  281.     Next b
  282.    
  283.     'Âèä³ëÿºìî ïîòð³áí³ ì³ñÿö³
  284.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  285.         "Month")
  286.         '.PivotItems("################").Visible = False
  287.         .PivotItems("Apr").Visible = False
  288.         .PivotItems("Aug").Visible = False
  289.         .PivotItems("Feb").Visible = False
  290.         .PivotItems("Jul").Visible = False
  291.         .PivotItems("Jun").Visible = False
  292.         .PivotItems("Mar").Visible = False
  293.         .PivotItems("May").Visible = False
  294.         .PivotItems("Nov").Visible = False
  295.         .PivotItems("Oct").Visible = False
  296.         .PivotItems("Sep").Visible = False
  297.         .PivotItems("Jan").Visible = False
  298.        
  299.         If Months_number <> 12 Then
  300.             For i = 1 To Months_number
  301.                 .PivotItems(months_a(i)).Visible = True
  302.             Next i
  303.                 .PivotItems("Dec").Visible = False
  304.         Else
  305.         For i = 1 To Months_number - 1
  306.                 .PivotItems(months_a(i)).Visible = True
  307.             Next i
  308.         End If
  309.        
  310.     End With
  311.    
  312.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Month" _
  313.         ).EnableMultiplePageItems = True
  314.    
  315.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  316.         "Êîëè÷åñòâî ïî ïîëþ 18-50 50+ EqGRP%").Function = xlSum
  317.    
  318.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  319.         "Category_eng").AutoSort xlDescending, "Ñóììà ïî ïîëþ 18-50 50+ EqGRP%", _
  320.         ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotColumnAxis. _
  321.         PivotLines(3), 1
  322.    
  323.     ActiveSheet.Range("$A$15").sort Key1:="R15C3", Order1:=xlDescending, Type _
  324.         :=xlSortValues, OrderCustom:=1, Orientation:=xlTopToBottom
  325.     ActiveWindow.SmallScroll Down:=3
  326.    
  327.    
  328. 'Îáðàõóíîê ê³ëüêîñò³ êàòåãîð³é
  329. Cells.Find(What:="Category_eng", After:=ActiveCell, LookIn:=xlFormulas, _
  330. LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  331. MatchCase:=False, SearchFormat:=False).Activate
  332.  
  333. 'Cells.Find(What:="Íàçâàíèÿ ñòðîê", After:=ActiveCell, LookIn:=xlFormulas _
  334.         , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  335.         MatchCase:=False, SearchFormat:=False).Activate
  336.  
  337.  
  338. For i = 1 To 1048560
  339.     If ActiveCell.Offset(i, 0).Value = "" Then Exit For
  340. Next i
  341.  
  342. cat_number_tv = i - 2
  343.  
  344.    
  345. 'Small cstegories and other
  346.  
  347. On Error Resume Next
  348.        
  349. ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  350. "Category_eng").PivotItems("Small Category").Position = cat_number_tv
  351. ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  352. "Category_eng").PivotItems("Other").Position = cat_number_tv
  353.        
  354. 'Êîï³þºìî íà ³íøèé ëèñò
  355.  
  356. Range(Cells(8, 1), Cells(7 + cat_number_tv, 3)).Select
  357. Selection.Copy
  358. Sheets("Òåìïëåéò").Select
  359. Range("C5").Select
  360. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  361. :=False, Transpose:=False
  362. Range("D11").Select
  363.    
  364. Cells(1, 1).Value = Year
  365. Cells(2, 1).Value = Month
  366. Cells(3, 1).Value = Year_prev
  367. Cells(4, 1).Value = Month_prev
  368.    
  369.    
  370. Sheets("Ëèñò1").Select
  371.    
  372. 'Äî ïîïåðåäíüîãî ì³ñÿöÿ
  373.  
  374.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  375.         "Month")
  376.         .Orientation = xlColumnField
  377.         .Position = 2
  378.     End With
  379.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  380.         "Year")
  381.         .Orientation = xlPageField
  382.         .Position = 1
  383.     End With
  384.  
  385.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Year") _
  386.         .CurrentPage = "(All)"
  387.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  388.         "Year")
  389.         .PivotItems(Year_prev).Visible = False
  390.     End With
  391.    
  392. For i = 1 To Months_number - 2
  393.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  394.         "Month")
  395.         .PivotItems(months_a(i)).Visible = False
  396.     End With
  397.        
  398. 'Next i
  399.      
  400. 'Copy/Paste
  401. Cells.Find(What:=Month, After:=ActiveCell, LookIn:=xlFormulas, _
  402.         LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  403.         MatchCase:=False, SearchFormat:=False).Activate
  404. c = ActiveCell.Column
  405. If c <= 1 Then c = enter1
  406. For f = 1 To 25
  407.     If Cells(ActiveCell.Row, f).Value = Month_prev Then
  408.         c1 = f
  409.         Exit For
  410.     End If
  411. Next f
  412. If c1 <= 1 Then c1 = enter2
  413.  
  414. For i = 1 To cat_number_tv
  415.     cat = Worksheets(3).Cells(4 + i, 3).Value
  416.    
  417.     For j = 1 To cat_number_tv
  418.         If Cells(7 + j, 1).Value = cat Then
  419.             Cells(7 + j, c).Select
  420.             Selection.Copy
  421.             Sheets("Òåìïëåéò").Select
  422.             Cells(4 + i, 7).Select
  423.             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  424.             :=False, Transpose:=False
  425.             Sheets("Ëèñò1").Select
  426.            
  427.             Cells(7 + j, c1).Select
  428.             Selection.Copy
  429.             Sheets("Òåìïëåéò").Select
  430.             Cells(4 + i, 6).Select
  431.             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  432.             :=False, Transpose:=False
  433.             Sheets("Ëèñò1").Select
  434.         End If
  435.     Next j
  436. Next i
  437.  
  438.  
  439. 'Î÷èùåííÿ çâåäåíî¿
  440.  
  441.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  442.         ).CurrentPage = "(All)"
  443.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  444.         "media")
  445.         '.PivotItems("################").Visible = True
  446.         .PivotItems("Internet").Visible = True
  447.         .PivotItems("Outdoor").Visible = True
  448.         .PivotItems("Press").Visible = True
  449.         .PivotItems("Radio").Visible = True
  450.     End With
  451.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  452.         "Spot_Type").CurrentPage = "(All)"
  453.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  454.         "Spot_Type")
  455.         '.PivotItems("################").Visible = True
  456.         .PivotItems("banner").Visible = True
  457.         .PivotItems("video").Visible = True
  458.         .PivotItems("àâòîïðîìîóøí").Visible = True
  459.         .PivotItems("àâòîïðîìîóøí ÏÝ").Visible = True
  460.         .PivotItems("áåãóùàÿ ñòðîêà").Visible = True
  461.         .PivotItems("Áðåíäèíã").Visible = True
  462.         .PivotItems("Ãàçåòà").Visible = True
  463.         .PivotItems("ãîëîñîâàíèå ïî òåëåôîíó").Visible = True
  464.         .PivotItems("ãîëîñîâàíèå ïî òåëåôîíó ÏÝ").Visible = True
  465.         .PivotItems("Ãðàôè÷åñêàÿ ìîäåëü").Visible = True
  466.         .PivotItems("Æóðíàë").Visible = True
  467.         .PivotItems("Èíôîðìàöèîííûé ñþæåò").Visible = True
  468.         .PivotItems("Èñïîëüçîâàíèå ðåêâèçèòà").Visible = True
  469.         .PivotItems("êëèï ñîöèàëüíîé ðåêëàìû").Visible = True
  470.         .PivotItems("ëîãîòèï").Visible = True
  471.         .PivotItems("Ëîãîòèï+Äèêòîð").Visible = True
  472.         .PivotItems("Ïëàøêà").Visible = True
  473.         .PivotItems("Ñïîíñîðñêàÿ áåãóùàÿ ñòðîêà").Visible = True
  474.         .PivotItems("Ñïîíñîðñêàÿ áëàãîäàðíîñòü â òèòðàõ").Visible = True
  475.         .PivotItems("Ñïîíñîðñêàÿ äèíàìè÷åñêàÿ çàñòàâêà").Visible = True
  476.         .PivotItems("ñïîíñîðñêàÿ çàñòàâêà").Visible = True
  477.         .PivotItems("ñïîíñîðñêàÿ òèòð-çàñòàâêà").Visible = True
  478.         .PivotItems("Ñïîíñîðñêèé ðåêâèçèò").Visible = True
  479.         .PivotItems("Ñïîíñîðñêîå îáúÿâëåíèå").Visible = True
  480.         .PivotItems("Èíôîðìàöèîí=ûé ñþæåò").Visible = True
  481.         .PivotItems("ïðåññà ìàêåò").Visible = True
  482.         .PivotItems("ñïîíñîðñêèé êëèï").Visible = True
  483.     End With
  484.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  485.         "Spot_Type")
  486.         .PivotItems("Ñïîíñîðñêîå îáúÿâëåíèå").Visible = True
  487.         .PivotItems("òåëåïðîäàæà").Visible = True
  488.         .PivotItems("óïîìèíàíèå â òèòðàõ").Visible = True
  489.         .PivotItems("(blank)").Visible = True
  490.     End With
  491.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Year") _
  492.         .CurrentPage = "(All)"
  493.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  494.         "Year")
  495.         '.PivotItems("2014").Visible = True
  496.         .PivotItems(Year_prev).Visible = True
  497.     End With
  498.    
  499.     For b = 2009 To Year - 2
  500.      
  501.    With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  502.         "Year")
  503.         .PivotItems(CStr(b)).Visible = True
  504.     End With
  505.    
  506.     Next b
  507.    
  508.    
  509.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  510.         "Month")
  511.         '.PivotItems("################").Visible = True
  512.          For k = 1 To 12
  513.         .PivotItems(months_a(k)).Visible = True
  514.         Next k
  515.     End With
  516.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  517.         "Category_eng")
  518.         '.PivotItems("################").Visible = True
  519.         .PivotItems("Advertising").Visible = True
  520.         .PivotItems("Mass Media").Visible = True
  521.         .PivotItems("Social").Visible = True
  522.     End With
  523.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Month" _
  524.         ).Orientation = xlHidden
  525.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  526.         "Êîëè÷åñòâî ïî ïîëþ 18-50 50+ EqGRP%").Orientation = xlHidden
  527.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  528.         ).Orientation = xlHidden
  529.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  530.         "Spot_Type").Orientation = xlHidden
  531.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Year") _
  532.         .Orientation = xlHidden
  533.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  534.         "Category_eng").Orientation = xlHidden
  535.     Rows("1:2").Select
  536.     Selection.delete Shift:=xlUp
  537.     Range("A4").Select
  538.    
  539.  
  540. End Sub
  541.  
  542. Sub TV_advertisers()
  543. 'Advertisers
  544.  
  545. 'Filters
  546.  
  547.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").AddDataField _
  548.         ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  549.         "18-50 50+ EqGRP%"), "Êîëè÷åñòâî ïî ïîëþ 18-50 50+ EqGRP%", xlCount
  550.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  551.         "Category_eng")
  552.         .Orientation = xlPageField
  553.         .Position = 1
  554.     End With
  555.    
  556.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  557.         "Good_Advertiser")
  558.         .Orientation = xlRowField
  559.         .Position = 1
  560.     End With
  561.    
  562.    
  563.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  564.         "media")
  565.         .Orientation = xlPageField
  566.         .Position = 1
  567.     End With
  568.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  569.         "Month")
  570.         .Orientation = xlPageField
  571.         .Position = 1
  572.     End With
  573.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  574.         "Year")
  575.         .Orientation = xlColumnField
  576.         .Position = 1
  577.     End With
  578.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  579.         "Spot_Type")
  580.         .Orientation = xlPageField
  581.         .Position = 2
  582.     End With
  583.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  584.         "Category_eng").CurrentPage = "(All)"
  585.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  586.         "Category_eng")
  587.         '.PivotItems("################").Visible = False
  588.         .PivotItems("Advertising").Visible = False
  589.         .PivotItems("Mass Media").Visible = False
  590.         .PivotItems("Social").Visible = False
  591.     End With
  592.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  593.         "Category_eng").EnableMultiplePageItems = True
  594.    
  595.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  596.         ).CurrentPage = "(All)"
  597.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  598.         "media")
  599.         '.PivotItems("################").Visible = False
  600.         .PivotItems("Internet").Visible = False
  601.         .PivotItems("Outdoor").Visible = False
  602.         .PivotItems("Press").Visible = False
  603.         .PivotItems("Radio").Visible = False
  604.     End With
  605.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  606.         ).EnableMultiplePageItems = True
  607.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  608.         "Spot_Type").CurrentPage = "(All)"
  609.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  610.         "Spot_Type")
  611.         '.PivotItems("################").Visible = False
  612.          .PivotItems("banner").Visible = False
  613.         .PivotItems("video").Visible = False
  614.         .PivotItems("àâòîïðîìîóøí").Visible = False
  615.         .PivotItems("àâòîïðîìîóøí ÏÝ").Visible = False
  616.         .PivotItems("áåãóùàÿ ñòðîêà").Visible = False
  617.         .PivotItems("Áðåíäèíã").Visible = False
  618.         .PivotItems("Ãàçåòà").Visible = False
  619.         .PivotItems("ãîëîñîâàíèå ïî òåëåôîíó").Visible = False
  620.         .PivotItems("ãîëîñîâàíèå ïî òåëåôîíó ÏÝ").Visible = False
  621.         .PivotItems("Ãðàôè÷åñêàÿ ìîäåëü").Visible = False
  622.         .PivotItems("Æóðíàë").Visible = False
  623.         .PivotItems("Èíôîðìàöèîííûé ñþæåò").Visible = False
  624.         .PivotItems("Èñïîëüçîâàíèå ðåêâèçèòà").Visible = False
  625.         .PivotItems("êëèï ñîöèàëüíîé ðåêëàìû").Visible = False
  626.         .PivotItems("ëîãîòèï").Visible = False
  627.         .PivotItems("Ëîãîòèï+Äèêòîð").Visible = False
  628.         .PivotItems("Ïëàøêà").Visible = False
  629.         .PivotItems("Ñïîíñîðñêàÿ áåãóùàÿ ñòðîêà").Visible = False
  630.         .PivotItems("Ñïîíñîðñêàÿ áëàãîäàðíîñòü â òèòðàõ").Visible = False
  631.         .PivotItems("Ñïîíñîðñêàÿ äèíàìè÷åñêàÿ çàñòàâêà").Visible = False
  632.         .PivotItems("ñïîíñîðñêàÿ çàñòàâêà").Visible = False
  633.         .PivotItems("ñïîíñîðñêàÿ òèòð-çàñòàâêà").Visible = False
  634.         .PivotItems("Ñïîíñîðñêèé ðåêâèçèò").Visible = False
  635.         .PivotItems("Ñïîíñîðñêîå îáúÿâëåíèå").Visible = False
  636.         .PivotItems("Èíôîðìàöèîí=ûé ñþæåò").Visible = False
  637.         .PivotItems("ïðåññà ìàêåò").Visible = False
  638.         .PivotItems("ñïîíñîðñêèé êëèï").Visible = False
  639.     End With
  640.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  641.         "Spot_Type")
  642.         .PivotItems("òåëåïðîäàæà").Visible = False
  643.         .PivotItems("óïîìèíàíèå â òèòðàõ").Visible = False
  644.         .PivotItems("(blank)").Visible = False
  645.     End With
  646.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  647.         "Spot_Type").EnableMultiplePageItems = True
  648.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Month" _
  649.         ).CurrentPage = "(All)"
  650.        
  651.        
  652. 'Ïåð³îä äî ïåð³îäó ïîïåðåäíüîãî ðîêó
  653.  
  654.    For b = 2009 To Year - 2
  655.      
  656.    With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  657.         "Year")
  658.         .PivotItems(CStr(b)).Visible = False
  659.     End With
  660.    
  661.     Next b
  662.    
  663.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  664.         "Month")
  665.         '.PivotItems("################").Visible = False
  666.         .PivotItems("Apr").Visible = False
  667.         .PivotItems("Aug").Visible = False
  668.         .PivotItems("Feb").Visible = False
  669.         .PivotItems("Jul").Visible = False
  670.         .PivotItems("Jun").Visible = False
  671.         .PivotItems("Mar").Visible = False
  672.         .PivotItems("May").Visible = False
  673.         .PivotItems("Nov").Visible = False
  674.         .PivotItems("Oct").Visible = False
  675.         .PivotItems("Sep").Visible = False
  676.         .PivotItems("Jan").Visible = False
  677.        
  678.         If Months_number <> 12 Then
  679.             For i = 1 To Months_number
  680.                 .PivotItems(months_a(i)).Visible = True
  681.             Next i
  682.                 .PivotItems("Dec").Visible = False
  683.         Else
  684.         For i = 1 To Months_number - 1
  685.                 .PivotItems(months_a(i)).Visible = True
  686.             Next i
  687.         End If
  688.        
  689.     End With
  690.    
  691.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Month" _
  692.         ).EnableMultiplePageItems = True
  693.    
  694.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  695.         "Êîëè÷åñòâî ïî ïîëþ 18-50 50+ EqGRP%").Function = xlSum
  696.    
  697.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  698.         "Good_Advertiser").AutoSort xlDescending, "Ñóììà ïî ïîëþ 18-50 50+ EqGRP%", _
  699.         ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotColumnAxis. _
  700.         PivotLines(3), 1
  701.    
  702.     ActiveSheet.Range("$A$15").sort Key1:="R15C3", Order1:=xlDescending, Type _
  703.         :=xlSortValues, OrderCustom:=1, Orientation:=xlTopToBottom
  704.     ActiveWindow.SmallScroll Down:=3
  705.    
  706.    
  707. 'Îáðàõóíîê ê³ëüêîñò³ ðåêëàìîäàâö³â
  708. Cells.Find(What:="Good_Advertiser", After:=ActiveCell, LookIn:=xlFormulas, _
  709. LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  710. MatchCase:=False, SearchFormat:=False).Activate
  711.  
  712. For i = 1 To 1048560
  713.     If ActiveCell.Offset(i, 0).Value = "" Then Exit For
  714. Next i
  715.  
  716. adv_number_tv = i - 2
  717.  
  718.    
  719. 'Small cstegories and other
  720.  
  721. On Error Resume Next
  722.        
  723. ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  724. "Good_Advertiser").PivotItems("Small advertiser").Position = adv_number_tv
  725. ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  726. "Good_Advertiser").PivotItems("Other").Position = adv_number_tv
  727.        
  728. 'Êîï³þºìî íà ³íøèé ëèñò
  729.  
  730. Range(Cells(8, 1), Cells(7 + adv_number_tv, 3)).Select
  731. Selection.Copy
  732. Sheets("Òåìïëåéò").Select
  733. Range("I5").Select
  734. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  735. :=False, Transpose:=False
  736. Range("D11").Select
  737.    
  738.    
  739. Sheets("Ëèñò1").Select
  740.    
  741. 'Äî ïîïåðåäíüîãî ì³ñÿöÿ
  742.  
  743.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  744.         "Month")
  745.         .Orientation = xlColumnField
  746.         .Position = 2
  747.     End With
  748.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  749.         "Year")
  750.         .Orientation = xlPageField
  751.         .Position = 1
  752.     End With
  753.  
  754.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Year") _
  755.         .CurrentPage = "(All)"
  756.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  757.         "Year")
  758.         .PivotItems(Year_prev).Visible = False
  759.     End With
  760.    
  761. For i = 1 To Months_number - 2
  762.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  763.         "Month")
  764.         .PivotItems(months_a(i)).Visible = False
  765.     End With
  766.        
  767. Next i
  768.      
  769. 'Copy/Paste
  770.  Cells.Find(What:=Month, After:=ActiveCell, LookIn:=xlFormulas, _
  771.         LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  772.         MatchCase:=False, SearchFormat:=False).Activate
  773. c = ActiveCell.Column
  774. If c <= 1 Then c = enter1
  775.  
  776. For f = 1 To 25
  777.     If Cells(ActiveCell.Row, f).Value = Month_prev Then
  778.         c1 = f
  779.         Exit For
  780.     End If
  781. Next f
  782. If c1 <= 1 Then c1 = enter2
  783. For i = 1 To adv_number_tv
  784.     adv = Worksheets(3).Cells(4 + i, 9).Value
  785.    
  786.     For j = 1 To adv_number_tv
  787.         If Cells(7 + j, 1).Value = adv Then
  788.             Cells(7 + j, c).Select
  789.             Selection.Copy
  790.             Sheets("Òåìïëåéò").Select
  791.             Cells(4 + i, 13).Select
  792.             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  793.             :=False, Transpose:=False
  794.             Sheets("Ëèñò1").Select
  795.            
  796.             Cells(7 + j, c1).Select
  797.             Selection.Copy
  798.             Sheets("Òåìïëåéò").Select
  799.             Cells(4 + i, 12).Select
  800.             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  801.             :=False, Transpose:=False
  802.             Sheets("Ëèñò1").Select
  803.         End If
  804.     Next j
  805. Next i
  806.  
  807. 'Î÷èùåííÿ çâåäåíî¿
  808.  
  809.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  810.         ).CurrentPage = "(All)"
  811.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  812.         "media")
  813.         '.PivotItems("################").Visible = True
  814.         .PivotItems("Internet").Visible = True
  815.         .PivotItems("Outdoor").Visible = True
  816.         .PivotItems("Press").Visible = True
  817.         .PivotItems("Radio").Visible = True
  818.     End With
  819.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  820.         "Spot_Type").CurrentPage = "(All)"
  821.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  822.         "Spot_Type")
  823.         '.PivotItems("################").Visible = True
  824.         .PivotItems("banner").Visible = True
  825.         .PivotItems("video").Visible = True
  826.         .PivotItems("àâòîïðîìîóøí").Visible = True
  827.         .PivotItems("àâòîïðîìîóøí ÏÝ").Visible = True
  828.         .PivotItems("áåãóùàÿ ñòðîêà").Visible = True
  829.         .PivotItems("Áðåíäèíã").Visible = True
  830.         .PivotItems("Ãàçåòà").Visible = True
  831.         .PivotItems("ãîëîñîâàíèå ïî òåëåôîíó").Visible = True
  832.         .PivotItems("ãîëîñîâàíèå ïî òåëåôîíó ÏÝ").Visible = True
  833.         .PivotItems("Ãðàôè÷åñêàÿ ìîäåëü").Visible = True
  834.         .PivotItems("Æóðíàë").Visible = True
  835.         .PivotItems("Èíôîðìàöèîííûé ñþæåò").Visible = True
  836.         .PivotItems("Èñïîëüçîâàíèå ðåêâèçèòà").Visible = True
  837.         .PivotItems("êëèï ñîöèàëüíîé ðåêëàìû").Visible = True
  838.         .PivotItems("ëîãîòèï").Visible = True
  839.         .PivotItems("Ëîãîòèï+Äèêòîð").Visible = True
  840.         .PivotItems("Ïëàøêà").Visible = True
  841.         .PivotItems("Ñïîíñîðñêàÿ áåãóùàÿ ñòðîêà").Visible = True
  842.         .PivotItems("Ñïîíñîðñêàÿ áëàãîäàðíîñòü â òèòðàõ").Visible = True
  843.         .PivotItems("Ñïîíñîðñêàÿ äèíàìè÷åñêàÿ çàñòàâêà").Visible = True
  844.         .PivotItems("ñïîíñîðñêàÿ çàñòàâêà").Visible = True
  845.         .PivotItems("ñïîíñîðñêàÿ òèòð-çàñòàâêà").Visible = True
  846.         .PivotItems("Ñïîíñîðñêèé ðåêâèçèò").Visible = True
  847.         .PivotItems("Ñïîíñîðñêîå îáúÿâëåíèå").Visible = True
  848.         .PivotItems("Èíôîðìàöèîí=ûé ñþæåò").Visible = True
  849.         .PivotItems("ïðåññà ìàêåò").Visible = True
  850.         .PivotItems("ñïîíñîðñêèé êëèï").Visible = True
  851.     End With
  852.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  853.         "Spot_Type")
  854.         .PivotItems("Ñïîíñîðñêîå îáúÿâëåíèå").Visible = True
  855.         .PivotItems("òåëåïðîäàæà").Visible = True
  856.         .PivotItems("óïîìèíàíèå â òèòðàõ").Visible = True
  857.         .PivotItems("(blank)").Visible = True
  858.     End With
  859.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Year") _
  860.         .CurrentPage = "(All)"
  861.     For b = 2009 To Year - 1
  862.      
  863.    With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  864.         "Year")
  865.         .PivotItems(CStr(b)).Visible = True
  866.     End With
  867.    
  868.     Next b
  869.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  870.         "Month")
  871.         '.PivotItems("################").Visible = True
  872.         For k = 1 To 12
  873.         .PivotItems(months_a(k)).Visible = True
  874.         Next k
  875.     End With
  876.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  877.         "Category_eng")
  878.         '.PivotItems("################").Visible = True
  879.         .PivotItems("Advertising").Visible = True
  880.         .PivotItems("Mass Media").Visible = True
  881.         .PivotItems("Social").Visible = True
  882.     End With
  883.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Month" _
  884.         ).Orientation = xlHidden
  885.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  886.         "Êîëè÷åñòâî ïî ïîëþ 18-50 50+ EqGRP%").Orientation = xlHidden
  887.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  888.         ).Orientation = xlHidden
  889.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  890.         "Spot_Type").Orientation = xlHidden
  891.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Year") _
  892.         .Orientation = xlHidden
  893.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  894.         "Category_eng").Orientation = xlHidden
  895.         ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  896.         "Good_Advertiser").Orientation = xlHidden
  897.     Rows("1:2").Select
  898.     Selection.delete Shift:=xlUp
  899.     Range("A4").Select
  900.    
  901.     End Sub
  902.  
  903. Sub Internet_categories(ByVal typespot As String)
  904.  
  905.  
  906. type_spot = typespot
  907. If type_spot = "video" Then
  908.     type_hide = "banner"
  909.     col_start = 16
  910. Else: type_hide = "video"
  911.     col_start = 29
  912. End If
  913.  
  914. 'Filters
  915.  
  916.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").AddDataField _
  917.         ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  918.         "Gross_budget_UAH"), "Êîëè÷åñòâî ïî ïîëþ Gross_budget_UAH", xlCount
  919.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  920.         "Category_eng")
  921.         .Orientation = xlPageField
  922.         .Position = 1
  923.     End With
  924.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  925.         "media")
  926.         .Orientation = xlPageField
  927.         .Position = 1
  928.     End With
  929.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  930.         "Month")
  931.         .Orientation = xlPageField
  932.         .Position = 1
  933.     End With
  934.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  935.         "Year")
  936.         .Orientation = xlColumnField
  937.         .Position = 1
  938.     End With
  939.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  940.         "Spot_Type")
  941.         .Orientation = xlPageField
  942.         .Position = 2
  943.     End With
  944.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  945.         "Category_eng").CurrentPage = "(All)"
  946.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  947.         "Category_eng")
  948.         '.PivotItems("################").Visible = False
  949.         .PivotItems("Advertising").Visible = False
  950.         .PivotItems("Mass Media").Visible = False
  951.         .PivotItems("Social").Visible = False
  952.     End With
  953.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  954.         "Category_eng").EnableMultiplePageItems = True
  955.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  956.         "Category_eng")
  957.         .Orientation = xlRowField
  958.         .Position = 1
  959.     End With
  960.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  961.         ).CurrentPage = "(All)"
  962.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  963.         "media")
  964.         '.PivotItems("################").Visible = False
  965.         .PivotItems("TV").Visible = False
  966.         .PivotItems("Outdoor").Visible = False
  967.         .PivotItems("Press").Visible = False
  968.         .PivotItems("Radio").Visible = False
  969.     End With
  970.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  971.         ).EnableMultiplePageItems = True
  972.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  973.         "Spot_Type").CurrentPage = "(All)"
  974.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  975.         "Spot_Type")
  976.         '.PivotItems("################").Visible = False
  977.         .PivotItems("êëèï").Visible = False
  978.         .PivotItems(type_hide).Visible = False
  979.         .PivotItems("àâòîïðîìîóøí").Visible = False
  980.         .PivotItems("àâòîïðîìîóøí ÏÝ").Visible = False
  981.         .PivotItems("áåãóùàÿ ñòðîêà").Visible = False
  982.         .PivotItems("Áðåíäèíã").Visible = False
  983.         .PivotItems("Ãàçåòà").Visible = False
  984.         .PivotItems("ãîëîñîâàíèå ïî òåëåôîíó").Visible = False
  985.         .PivotItems("ãîëîñîâàíèå ïî òåëåôîíó ÏÝ").Visible = False
  986.         .PivotItems("Ãðàôè÷åñêàÿ ìîäåëü").Visible = False
  987.         .PivotItems("Æóðíàë").Visible = False
  988.         .PivotItems("Èíôîðìàöèîííûé ñþæåò").Visible = False
  989.         .PivotItems("Èñïîëüçîâàíèå ðåêâèçèòà").Visible = False
  990.         .PivotItems("êëèï ñîöèàëüíîé ðåêëàìû").Visible = False
  991.         .PivotItems("ëîãîòèï").Visible = False
  992.         '.PivotItems("Ëîãîòèï+Äèêòîð").Visible = False
  993.         .PivotItems("Ïëàøêà").Visible = False
  994.         '.PivotItems("Ñïîíñîðñêàÿ áåãóùàÿ ñòðîêà").Visible = False
  995.         '.PivotItems("Ñïîíñîðñêàÿ áëàãîäàðíîñòü â òèòðàõ").Visible = False
  996.         .PivotItems("Ñïîíñîðñêàÿ äèíàìè÷åñêàÿ çàñòàâêà").Visible = False
  997.         .PivotItems("ñïîíñîðñêàÿ çàñòàâêà").Visible = False
  998.         .PivotItems("ñïîíñîðñêàÿ òèòð-çàñòàâêà").Visible = False
  999.         .PivotItems("Ñïîíñîðñêèé ðåêâèçèò").Visible = False
  1000.         .PivotItems("Ñïîíñîðñêîå îáúÿâëåíèå").Visible = False
  1001.     End With
  1002.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1003.         "Spot_Type")
  1004.         .PivotItems("òåëåïðîäàæà").Visible = False
  1005.         .PivotItems("óïîìèíàíèå â òèòðàõ").Visible = False
  1006.         .PivotItems("êëèï - ðåãèîí").Visible = False
  1007.         .PivotItems("(blank)").Visible = False
  1008.     End With
  1009.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1010.         "Spot_Type").EnableMultiplePageItems = True
  1011.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Month" _
  1012.         ).CurrentPage = "(All)"
  1013.        
  1014.        
  1015. 'Ïåð³îä äî ïåð³îäó ïîïåðåäíüîãî ðîêó
  1016.  
  1017.    'With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1018.         "Year")
  1019.         '.PivotItems("2014").Visible = False
  1020.     'End With
  1021.    
  1022.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1023.         "Month")
  1024.         '.PivotItems("################").Visible = False
  1025.         .PivotItems("Apr").Visible = False
  1026.         .PivotItems("Aug").Visible = False
  1027.         .PivotItems("Feb").Visible = False
  1028.         .PivotItems("Jul").Visible = False
  1029.         .PivotItems("Jun").Visible = False
  1030.         .PivotItems("Mar").Visible = False
  1031.         .PivotItems("May").Visible = False
  1032.         .PivotItems("Nov").Visible = False
  1033.         .PivotItems("Oct").Visible = False
  1034.         .PivotItems("Sep").Visible = False
  1035.         .PivotItems("Jan").Visible = False
  1036.        
  1037.         If Months_number <> 12 Then
  1038.             For i = 1 To Months_number
  1039.                 .PivotItems(months_a(i)).Visible = True
  1040.             Next i
  1041.                 .PivotItems("Dec").Visible = False
  1042.         Else
  1043.         For i = 1 To Months_number - 1
  1044.                 .PivotItems(months_a(i)).Visible = True
  1045.             Next i
  1046.         End If
  1047.        
  1048.     End With
  1049.    
  1050.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Month" _
  1051.         ).EnableMultiplePageItems = True
  1052.    
  1053.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1054.         "Êîëè÷åñòâî ïî ïîëþ Gross_budget_UAH").Function = xlSum
  1055.    
  1056.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1057.         "Category_eng").AutoSort xlDescending, "Ñóììà ïî ïîëþ Gross_budget_UAH", _
  1058.         ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotColumnAxis. _
  1059.         PivotLines(3), 1
  1060.    
  1061.     ActiveSheet.Range("$A$15").sort Key1:="R15C3", Order1:=xlDescending, Type _
  1062.         :=xlSortValues, OrderCustom:=1, Orientation:=xlTopToBottom
  1063.     ActiveWindow.SmallScroll Down:=3
  1064.    
  1065.    
  1066. 'Îáðàõóíîê ê³ëüêîñò³ êàòåãîð³é
  1067. Cells.Find(What:="Category_eng", After:=ActiveCell, LookIn:=xlFormulas, _
  1068. LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  1069. MatchCase:=False, SearchFormat:=False).Activate
  1070.  
  1071. For i = 1 To 1048560
  1072.     If ActiveCell.Offset(i, 0).Value = "" Then Exit For
  1073. Next i
  1074.  
  1075. cat_number_internet = i - 2
  1076.  
  1077.    
  1078. 'Small cstegories and other
  1079.  
  1080. On Error Resume Next
  1081.        
  1082. ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1083. "Category_eng").PivotItems("Small Category").Position = cat_number_internet
  1084. ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1085. "Category_eng").PivotItems("Other").Position = cat_number_internet
  1086.        
  1087. 'Êîï³þºìî íà ³íøèé ëèñò
  1088.  
  1089. Range(Cells(8, 1), Cells(7 + cat_number_internet, 3)).Select
  1090. Selection.Copy
  1091. Sheets("Òåìïëåéò").Select
  1092. Cells(5, col_start).Select
  1093. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  1094. :=False, Transpose:=False
  1095. Range("D11").Select
  1096.    
  1097. Sheets("Ëèñò1").Select
  1098.    
  1099. 'Äî ïîïåðåäíüîãî ì³ñÿöÿ
  1100.  
  1101.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1102.         "Month")
  1103.         .Orientation = xlColumnField
  1104.         .Position = 2
  1105.     End With
  1106.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1107.         "Year")
  1108.         .Orientation = xlPageField
  1109.         .Position = 1
  1110.     End With
  1111.  
  1112.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Year") _
  1113.         .CurrentPage = "(All)"
  1114.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1115.         "Year")
  1116.         .PivotItems(Year_prev).Visible = False
  1117.     End With
  1118.    
  1119. For i = 1 To Months_number - 2
  1120.       With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1121.         "Month")
  1122.         .PivotItems(months_a(i)).Visible = False
  1123.         End With
  1124.        
  1125. Next i
  1126.      
  1127. 'Copy/Paste
  1128. Cells.Find(What:=Month, After:=ActiveCell, LookIn:=xlFormulas, _
  1129. LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  1130. MatchCase:=False, SearchFormat:=False).Activate
  1131. c = ActiveCell.Column
  1132. If c <= 1 Then c = enter1
  1133. For f = 1 To 25
  1134.     If Cells(ActiveCell.Row, f).Value = Month_prev Then
  1135.         c1 = f
  1136.         Exit For
  1137.     End If
  1138. Next f
  1139. If c1 <= 1 Then c1 = enter2
  1140.  
  1141. For i = 1 To cat_number_internet
  1142.     cat = Worksheets(3).Cells(4 + i, col_start).Value
  1143.    
  1144.     For j = 1 To cat_number_internet
  1145.         If Cells(7 + j, 1).Value = cat Then
  1146.             Cells(7 + j, c).Select
  1147.             Selection.Copy
  1148.             Sheets("Òåìïëåéò").Select
  1149.             Cells(4 + i, col_start + 4).Select
  1150.             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  1151.             :=False, Transpose:=False
  1152.             Sheets("Ëèñò1").Select
  1153.            
  1154.             Cells(7 + j, c1).Select
  1155.             Selection.Copy
  1156.             Sheets("Òåìïëåéò").Select
  1157.             Cells(4 + i, col_start + 3).Select
  1158.             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  1159.             :=False, Transpose:=False
  1160.             Sheets("Ëèñò1").Select
  1161.         End If
  1162.     Next j
  1163. Next i
  1164.  
  1165. 'Î÷èùåííÿ çâåäåíî¿
  1166.  
  1167.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  1168.         ).CurrentPage = "(All)"
  1169.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1170.         "media")
  1171.         '.PivotItems("################").Visible = True
  1172.         .PivotItems("TV").Visible = True
  1173.         .PivotItems("Outdoor").Visible = True
  1174.         .PivotItems("Press").Visible = True
  1175.         .PivotItems("Radio").Visible = True
  1176.     End With
  1177.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1178.         "Spot_Type").CurrentPage = "(All)"
  1179.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1180.         "Spot_Type")
  1181.         '.PivotItems("################").Visible = True
  1182.         .PivotItems(type_hide).Visible = True
  1183.         .PivotItems("êëèï").Visible = True
  1184.         .PivotItems("àâòîïðîìîóøí").Visible = True
  1185.         .PivotItems("àâòîïðîìîóøí ÏÝ").Visible = True
  1186.         .PivotItems("áåãóùàÿ ñòðîêà").Visible = True
  1187.         .PivotItems("Áðåíäèíã").Visible = True
  1188.         .PivotItems("Ãàçåòà").Visible = True
  1189.         .PivotItems("ãîëîñîâàíèå ïî òåëåôîíó").Visible = True
  1190.         .PivotItems("ãîëîñîâàíèå ïî òåëåôîíó ÏÝ").Visible = True
  1191.         .PivotItems("Ãðàôè÷åñêàÿ ìîäåëü").Visible = True
  1192.         .PivotItems("Æóðíàë").Visible = True
  1193.         .PivotItems("Èíôîðìàöèîííûé ñþæåò").Visible = True
  1194.         .PivotItems("Èñïîëüçîâàíèå ðåêâèçèòà").Visible = True
  1195.         .PivotItems("êëèï ñîöèàëüíîé ðåêëàìû").Visible = True
  1196.         .PivotItems("ëîãîòèï").Visible = True
  1197.         '.PivotItems("Ëîãîòèï+Äèêòîð").Visible = True
  1198.         .PivotItems("Ïëàøêà").Visible = True
  1199.         '.PivotItems("Ñïîíñîðñêàÿ áåãóùàÿ ñòðîêà").Visible = True
  1200.         '.PivotItems("Ñïîíñîðñêàÿ áëàãîäàðíîñòü â òèòðàõ").Visible = True
  1201.         .PivotItems("Ñïîíñîðñêàÿ äèíàìè÷åñêàÿ çàñòàâêà").Visible = True
  1202.         .PivotItems("ñïîíñîðñêàÿ çàñòàâêà").Visible = True
  1203.         .PivotItems("ñïîíñîðñêàÿ òèòð-çàñòàâêà").Visible = True
  1204.         .PivotItems("Ñïîíñîðñêèé ðåêâèçèò").Visible = True
  1205.     End With
  1206.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1207.         "Spot_Type")
  1208.         .PivotItems("Ñïîíñîðñêîå îáúÿâëåíèå").Visible = True
  1209.         .PivotItems("òåëåïðîäàæà").Visible = True
  1210.         .PivotItems("óïîìèíàíèå â òèòðàõ").Visible = True
  1211.         .PivotItems("êëèï - ðåãèîí").Visible = True
  1212.         .PivotItems("(blank)").Visible = True
  1213.     End With
  1214.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Year") _
  1215.         .CurrentPage = "(All)"
  1216.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1217.         "Year")
  1218.         '.PivotItems("2014").Visible = True
  1219.         .PivotItems(Year_prev).Visible = True
  1220.     End With
  1221.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1222.         "Month")
  1223.         '.PivotItems("################").Visible = True
  1224.          For k = 1 To 12
  1225.         .PivotItems(months_a(k)).Visible = True
  1226.         Next k
  1227.     End With
  1228.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1229.         "Category_eng")
  1230.         '.PivotItems("################").Visible = True
  1231.         .PivotItems("Advertising").Visible = True
  1232.         .PivotItems("Mass Media").Visible = True
  1233.         .PivotItems("Social").Visible = True
  1234.     End With
  1235.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Month" _
  1236.         ).Orientation = xlHidden
  1237.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1238.         "Êîëè÷åñòâî ïî ïîëþ Gross_budget_UAH").Orientation = xlHidden
  1239.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  1240.         ).Orientation = xlHidden
  1241.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1242.         "Spot_Type").Orientation = xlHidden
  1243.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Year") _
  1244.         .Orientation = xlHidden
  1245.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1246.         "Category_eng").Orientation = xlHidden
  1247.     Rows("1:2").Select
  1248.     Selection.delete Shift:=xlUp
  1249.     Range("A4").Select
  1250.    
  1251.  
  1252.  
  1253.  
  1254. End Sub
  1255.  
  1256.  
  1257. Sub Internet_advertisers(ByVal typespot As String)
  1258. 'Advertisers
  1259.  
  1260. type_spot = typespot
  1261. If type_spot = "video" Then
  1262.     type_hide = "banner"
  1263.     col_start = 22
  1264. Else: type_hide = "video"
  1265.     col_start = 35
  1266. End If
  1267.  
  1268.  
  1269. 'Filters
  1270.  
  1271.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").AddDataField _
  1272.         ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1273.         "Gross_budget_UAH"), "Êîëè÷åñòâî ïî ïîëþ Gross_budget_UAH", xlCount
  1274.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1275.         "Category_eng")
  1276.         .Orientation = xlPageField
  1277.         .Position = 1
  1278.     End With
  1279.    
  1280.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1281.         "Good_Advertiser")
  1282.         .Orientation = xlRowField
  1283.         .Position = 1
  1284.     End With
  1285.    
  1286.    
  1287.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1288.         "media")
  1289.         .Orientation = xlPageField
  1290.         .Position = 1
  1291.     End With
  1292.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1293.         "Month")
  1294.         .Orientation = xlPageField
  1295.         .Position = 1
  1296.     End With
  1297.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1298.         "Year")
  1299.         .Orientation = xlColumnField
  1300.         .Position = 1
  1301.     End With
  1302.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1303.         "Spot_Type")
  1304.         .Orientation = xlPageField
  1305.         .Position = 2
  1306.     End With
  1307.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1308.         "Category_eng").CurrentPage = "(All)"
  1309.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1310.         "Category_eng")
  1311.         '.PivotItems("################").Visible = False
  1312.         .PivotItems("Advertising").Visible = False
  1313.         .PivotItems("Mass Media").Visible = False
  1314.         .PivotItems("Social").Visible = False
  1315.     End With
  1316.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1317.         "Category_eng").EnableMultiplePageItems = True
  1318.    
  1319.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  1320.         ).CurrentPage = "(All)"
  1321.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1322.         "media")
  1323.         '.PivotItems("################").Visible = False
  1324.         .PivotItems("TV").Visible = False
  1325.         .PivotItems("Outdoor").Visible = False
  1326.         .PivotItems("Press").Visible = False
  1327.         .PivotItems("Radio").Visible = False
  1328.     End With
  1329.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  1330.         ).EnableMultiplePageItems = True
  1331.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1332.         "Spot_Type").CurrentPage = "(All)"
  1333.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1334.         "Spot_Type")
  1335.         '.PivotItems("################").Visible = False
  1336.         .PivotItems(type_hide).Visible = False
  1337.         .PivotItems("êëèï").Visible = False
  1338.         .PivotItems("àâòîïðîìîóøí").Visible = False
  1339.         .PivotItems("àâòîïðîìîóøí ÏÝ").Visible = False
  1340.         .PivotItems("áåãóùàÿ ñòðîêà").Visible = False
  1341.         .PivotItems("Áðåíäèíã").Visible = False
  1342.         .PivotItems("Ãàçåòà").Visible = False
  1343.         .PivotItems("ãîëîñîâàíèå ïî òåëåôîíó").Visible = False
  1344.         .PivotItems("ãîëîñîâàíèå ïî òåëåôîíó ÏÝ").Visible = False
  1345.         .PivotItems("Ãðàôè÷åñêàÿ ìîäåëü").Visible = False
  1346.         .PivotItems("Æóðíàë").Visible = False
  1347.         .PivotItems("Èíôîðìàöèîííûé ñþæåò").Visible = False
  1348.         .PivotItems("Èñïîëüçîâàíèå ðåêâèçèòà").Visible = False
  1349.         .PivotItems("êëèï ñîöèàëüíîé ðåêëàìû").Visible = False
  1350.         .PivotItems("ëîãîòèï").Visible = False
  1351.         '.PivotItems("Ëîãîòèï+Äèêòîð").Visible = False
  1352.         .PivotItems("Ïëàøêà").Visible = False
  1353.         '.PivotItems("Ñïîíñîðñêàÿ áåãóùàÿ ñòðîêà").Visible = False
  1354.         '.PivotItems("Ñïîíñîðñêàÿ áëàãîäàðíîñòü â òèòðàõ").Visible = False
  1355.         .PivotItems("Ñïîíñîðñêàÿ äèíàìè÷åñêàÿ çàñòàâêà").Visible = False
  1356.         .PivotItems("ñïîíñîðñêàÿ çàñòàâêà").Visible = False
  1357.         .PivotItems("ñïîíñîðñêàÿ òèòð-çàñòàâêà").Visible = False
  1358.         .PivotItems("Ñïîíñîðñêèé ðåêâèçèò").Visible = False
  1359.         .PivotItems("Ñïîíñîðñêîå îáúÿâëåíèå").Visible = False
  1360.     End With
  1361.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1362.         "Spot_Type")
  1363.         .PivotItems("òåëåïðîäàæà").Visible = False
  1364.         .PivotItems("óïîìèíàíèå â òèòðàõ").Visible = False
  1365.         .PivotItems("êëèï - ðåãèîí").Visible = False
  1366.         .PivotItems("(blank)").Visible = False
  1367.     End With
  1368.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1369.         "Spot_Type").EnableMultiplePageItems = True
  1370.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Month" _
  1371.         ).CurrentPage = "(All)"
  1372.        
  1373.        
  1374. 'Ïåð³îä äî ïåð³îäó ïîïåðåäíüîãî ðîêó
  1375.  
  1376.     'With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1377.         "Year")
  1378.         '.PivotItems("2014").Visible = False
  1379.     'End With
  1380.    
  1381.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1382.         "Month")
  1383.         '.PivotItems("################").Visible = False
  1384.         .PivotItems("Apr").Visible = False
  1385.         .PivotItems("Aug").Visible = False
  1386.         .PivotItems("Feb").Visible = False
  1387.         .PivotItems("Jul").Visible = False
  1388.         .PivotItems("Jun").Visible = False
  1389.         .PivotItems("Mar").Visible = False
  1390.         .PivotItems("May").Visible = False
  1391.         .PivotItems("Nov").Visible = False
  1392.         .PivotItems("Oct").Visible = False
  1393.         .PivotItems("Sep").Visible = False
  1394.         .PivotItems("Jan").Visible = False
  1395.        
  1396.         If Months_number <> 12 Then
  1397.             For i = 1 To Months_number
  1398.                 .PivotItems(months_a(i)).Visible = True
  1399.             Next i
  1400.                 .PivotItems("Dec").Visible = False
  1401.         Else
  1402.         For i = 1 To Months_number - 1
  1403.                 .PivotItems(months_a(i)).Visible = True
  1404.             Next i
  1405.         End If
  1406.        
  1407.     End With
  1408.    
  1409.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Month" _
  1410.         ).EnableMultiplePageItems = True
  1411.    
  1412.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1413.         "Êîëè÷åñòâî ïî ïîëþ Gross_budget_UAH").Function = xlSum
  1414.    
  1415.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1416.         "Good_Advertiser").AutoSort xlDescending, "Ñóììà ïî ïîëþ Gross_budget_UAH", _
  1417.         ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotColumnAxis. _
  1418.         PivotLines(3), 1
  1419.    
  1420.     ActiveSheet.Range("$A$15").sort Key1:="R15C3", Order1:=xlDescending, Type _
  1421.         :=xlSortValues, OrderCustom:=1, Orientation:=xlTopToBottom
  1422.     ActiveWindow.SmallScroll Down:=3
  1423.    
  1424.    
  1425. 'Îáðàõóíîê ê³ëüêîñò³ ðåêëàìîäàâö³â
  1426. Cells.Find(What:="Good_Advertiser", After:=ActiveCell, LookIn:=xlFormulas, _
  1427. LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  1428. MatchCase:=False, SearchFormat:=False).Activate
  1429.  
  1430. For i = 1 To 1048560
  1431.     If ActiveCell.Offset(i, 0).Value = "" Then Exit For
  1432. Next i
  1433.  
  1434. adv_number_internet = i - 2
  1435.  
  1436.    
  1437. 'Small cstegories and other
  1438.  
  1439. On Error Resume Next
  1440.        
  1441. ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1442. "Good_Advertiser").PivotItems("Small advertiser").Position = adv_number_internet
  1443. ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1444. "Good_Advertiser").PivotItems("Other").Position = adv_number_internet
  1445.        
  1446. 'Êîï³þºìî íà ³íøèé ëèñò
  1447.  
  1448. Range(Cells(8, 1), Cells(7 + adv_number_internet, 3)).Select
  1449. Selection.Copy
  1450. Sheets("Òåìïëåéò").Select
  1451. Cells(5, col_start).Select
  1452. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  1453. :=False, Transpose:=False
  1454. Range("D11").Select
  1455.    
  1456.    
  1457. Sheets("Ëèñò1").Select
  1458.    
  1459. 'Äî ïîïåðåäíüîãî ì³ñÿöÿ
  1460.  
  1461.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1462.         "Month")
  1463.         .Orientation = xlColumnField
  1464.         .Position = 2
  1465.     End With
  1466.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1467.         "Year")
  1468.         .Orientation = xlPageField
  1469.         .Position = 1
  1470.     End With
  1471.  
  1472.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Year") _
  1473.         .CurrentPage = "(All)"
  1474.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1475.         "Year")
  1476.         .PivotItems(Year_prev).Visible = False
  1477.     End With
  1478.    
  1479. For i = 1 To Months_number - 2
  1480.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1481.         "Month")
  1482.         .PivotItems(months_a(i)).Visible = False
  1483.     End With
  1484.        
  1485. Next i
  1486.      
  1487. 'Copy/Paste
  1488. Cells.Find(What:=Month, After:=ActiveCell, LookIn:=xlFormulas, _
  1489. LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  1490. MatchCase:=False, SearchFormat:=False).Activate
  1491. c = ActiveCell.Column
  1492. If c <= 1 Then c = enter1
  1493. If c = 1 Then c = enter1
  1494. For f = 1 To 25
  1495.     If Cells(ActiveCell.Row, f).Value = Month_prev Then
  1496.         c1 = f
  1497.         Exit For
  1498.     End If
  1499. Next f
  1500. If c1 <= 1 Or c1 = c Then c1 = enter2
  1501. For i = 1 To adv_number_internet
  1502.     adv = Worksheets(3).Cells(4 + i, col_start).Value
  1503.    
  1504.     For j = 1 To adv_number_internet
  1505.         If Cells(7 + j, 1).Value = adv Then
  1506.             Cells(7 + j, c).Select
  1507.             Selection.Copy
  1508.             Sheets("Òåìïëåéò").Select
  1509.             Cells(4 + i, col_start + 4).Select
  1510.             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  1511.             :=False, Transpose:=False
  1512.             Sheets("Ëèñò1").Select
  1513.            
  1514.             Cells(7 + j, c1).Select
  1515.             Selection.Copy
  1516.             Sheets("Òåìïëåéò").Select
  1517.             Cells(4 + i, col_start + 3).Select
  1518.             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  1519.             :=False, Transpose:=False
  1520.             Sheets("Ëèñò1").Select
  1521.         End If
  1522.     Next j
  1523. Next i
  1524.  
  1525. 'Î÷èùåííÿ çâåäåíî¿
  1526.  
  1527.  ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  1528.         ).CurrentPage = "(All)"
  1529.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1530.         "media")
  1531.         '.PivotItems("################").Visible = True
  1532.         .PivotItems("TV").Visible = True
  1533.         .PivotItems("Outdoor").Visible = True
  1534.         .PivotItems("Press").Visible = True
  1535.         .PivotItems("Radio").Visible = True
  1536.     End With
  1537.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1538.         "Spot_Type").CurrentPage = "(All)"
  1539.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1540.         "Spot_Type")
  1541.         '.PivotItems("################").Visible = True
  1542.         .PivotItems(type_hide).Visible = True
  1543.         .PivotItems("êëèï").Visible = True
  1544.         .PivotItems("àâòîïðîìîóøí").Visible = True
  1545.         .PivotItems("àâòîïðîìîóøí ÏÝ").Visible = True
  1546.         .PivotItems("áåãóùàÿ ñòðîêà").Visible = True
  1547.         .PivotItems("Áðåíäèíã").Visible = True
  1548.         .PivotItems("Ãàçåòà").Visible = True
  1549.         .PivotItems("ãîëîñîâàíèå ïî òåëåôîíó").Visible = True
  1550.         .PivotItems("ãîëîñîâàíèå ïî òåëåôîíó ÏÝ").Visible = True
  1551.         .PivotItems("Ãðàôè÷åñêàÿ ìîäåëü").Visible = True
  1552.         .PivotItems("Æóðíàë").Visible = True
  1553.         .PivotItems("Èíôîðìàöèîííûé ñþæåò").Visible = True
  1554.         .PivotItems("Èñïîëüçîâàíèå ðåêâèçèòà").Visible = True
  1555.         .PivotItems("êëèï ñîöèàëüíîé ðåêëàìû").Visible = True
  1556.         .PivotItems("ëîãîòèï").Visible = True
  1557.         '.PivotItems("Ëîãîòèï+Äèêòîð").Visible = True
  1558.         .PivotItems("Ïëàøêà").Visible = True
  1559.         '.PivotItems("Ñïîíñîðñêàÿ áåãóùàÿ ñòðîêà").Visible = True
  1560.         '.PivotItems("Ñïîíñîðñêàÿ áëàãîäàðíîñòü â òèòðàõ").Visible = True
  1561.         .PivotItems("Ñïîíñîðñêàÿ äèíàìè÷åñêàÿ çàñòàâêà").Visible = True
  1562.         .PivotItems("ñïîíñîðñêàÿ çàñòàâêà").Visible = True
  1563.         .PivotItems("ñïîíñîðñêàÿ òèòð-çàñòàâêà").Visible = True
  1564.         .PivotItems("Ñïîíñîðñêèé ðåêâèçèò").Visible = True
  1565.     End With
  1566.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1567.         "Spot_Type")
  1568.         .PivotItems("Ñïîíñîðñêîå îáúÿâëåíèå").Visible = True
  1569.         .PivotItems("òåëåïðîäàæà").Visible = True
  1570.         .PivotItems("óïîìèíàíèå â òèòðàõ").Visible = True
  1571.         .PivotItems("êëèï - ðåãèîí").Visible = True
  1572.         .PivotItems("(blank)").Visible = True
  1573.     End With
  1574.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Year") _
  1575.         .CurrentPage = "(All)"
  1576.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1577.         "Year")
  1578.         '.PivotItems("2014").Visible = True
  1579.         .PivotItems(Year_prev).Visible = True
  1580.     End With
  1581.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1582.         "Month")
  1583.         '.PivotItems("################").Visible = True
  1584.         For k = 1 To 12
  1585.         .PivotItems(months_a(k)).Visible = True
  1586.         Next k
  1587.     End With
  1588.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1589.         "Category_eng")
  1590.         '.PivotItems("################").Visible = True
  1591.         .PivotItems("Advertising").Visible = True
  1592.         .PivotItems("Mass Media").Visible = True
  1593.         .PivotItems("Social").Visible = True
  1594.     End With
  1595.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Month" _
  1596.         ).Orientation = xlHidden
  1597.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1598.         "Êîëè÷åñòâî ïî ïîëþ Gross_budget_UAH").Orientation = xlHidden
  1599.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  1600.         ).Orientation = xlHidden
  1601.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1602.         "Spot_Type").Orientation = xlHidden
  1603.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Year") _
  1604.         .Orientation = xlHidden
  1605.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1606.         "Category_eng").Orientation = xlHidden
  1607.         ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1608.         "Good_Advertiser").Orientation = xlHidden
  1609.     Rows("1:2").Select
  1610.     Selection.delete Shift:=xlUp
  1611.     Range("A4").Select
  1612.    
  1613.     End Sub
  1614.  
  1615. Sub Other_categories(ByVal media As String)
  1616.  
  1617. If media = "Outdoor" Then
  1618.     col_start = 42
  1619. ElseIf media = "Press" Then
  1620.     col_start = 55
  1621. ElseIf media = "Radio" Then
  1622.     col_start = 68
  1623. End If
  1624.  
  1625.  
  1626. 'Filters
  1627.  
  1628.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").AddDataField _
  1629.         ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1630.         "Net_AdCoal_UAH"), "Êîëè÷åñòâî ïî ïîëþ Net_AdCoal_UAH", xlCount
  1631.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1632.         "Category_eng")
  1633.         .Orientation = xlPageField
  1634.         .Position = 1
  1635.     End With
  1636.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1637.         "media")
  1638.         .Orientation = xlPageField
  1639.         .Position = 1
  1640.     End With
  1641.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1642.         "Month")
  1643.         .Orientation = xlPageField
  1644.         .Position = 1
  1645.     End With
  1646.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1647.         "Year")
  1648.         .Orientation = xlColumnField
  1649.         .Position = 1
  1650.     End With
  1651.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1652.         "Spot_Type")
  1653.         .Orientation = xlPageField
  1654.         .Position = 2
  1655.     End With
  1656.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1657.         "Category_eng").CurrentPage = "(All)"
  1658.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1659.         "Category_eng")
  1660.         '.PivotItems("################").Visible = False
  1661.         .PivotItems("Advertising").Visible = False
  1662.         .PivotItems("Mass Media").Visible = False
  1663.         .PivotItems("Social").Visible = False
  1664.     End With
  1665.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1666.         "Category_eng").EnableMultiplePageItems = True
  1667.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1668.         "Category_eng")
  1669.         .Orientation = xlRowField
  1670.         .Position = 1
  1671.     End With
  1672.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  1673.         ).CurrentPage = "(All)"
  1674.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1675.         "media")
  1676.         '.PivotItems("################").Visible = False
  1677.         .PivotItems("Internet").Visible = False
  1678.         .PivotItems("Outdoor").Visible = False
  1679.         .PivotItems("Press").Visible = False
  1680.         .PivotItems("Radio").Visible = False
  1681.         .PivotItems(media).Visible = True
  1682.         .PivotItems("TV").Visible = False
  1683.     End With
  1684.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  1685.         ).EnableMultiplePageItems = True
  1686.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Month" _
  1687.         ).CurrentPage = "(All)"
  1688.        
  1689.        
  1690. 'Ïåð³îä äî ïåð³îäó ïîïåðåäíüîãî ðîêó
  1691.  
  1692.     For b = 2009 To Year - 2
  1693.      
  1694.    With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1695.         "Year")
  1696.         .PivotItems(CStr(b)).Visible = False
  1697.     End With
  1698.    
  1699.     Next b
  1700.    
  1701.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1702.         "Month")
  1703.         '.PivotItems("################").Visible = False
  1704.         .PivotItems("Apr").Visible = False
  1705.         .PivotItems("Aug").Visible = False
  1706.         .PivotItems("Feb").Visible = False
  1707.         .PivotItems("Jul").Visible = False
  1708.         .PivotItems("Jun").Visible = False
  1709.         .PivotItems("Mar").Visible = False
  1710.         .PivotItems("May").Visible = False
  1711.         .PivotItems("Nov").Visible = False
  1712.         .PivotItems("Oct").Visible = False
  1713.         .PivotItems("Sep").Visible = False
  1714.         .PivotItems("Jan").Visible = False
  1715.        
  1716.         If Months_number <> 12 Then
  1717.             For i = 1 To Months_number
  1718.                 .PivotItems(months_a(i)).Visible = True
  1719.             Next i
  1720.                 .PivotItems("Dec").Visible = False
  1721.         Else
  1722.         For i = 1 To Months_number - 1
  1723.                 .PivotItems(months_a(i)).Visible = True
  1724.             Next i
  1725.         End If
  1726.        
  1727.     End With
  1728.    
  1729.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Month" _
  1730.         ).EnableMultiplePageItems = True
  1731.    
  1732.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1733.         "Êîëè÷åñòâî ïî ïîëþ Net_AdCoal_UAH").Function = xlSum
  1734.    
  1735.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1736.         "Category_eng").AutoSort xlDescending, "Ñóììà ïî ïîëþ Net_AdCoal_UAH", _
  1737.         ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotColumnAxis. _
  1738.         PivotLines(3), 1
  1739.    
  1740.     ActiveSheet.Range("$A$15").sort Key1:="R15C3", Order1:=xlDescending, Type _
  1741.         :=xlSortValues, OrderCustom:=1, Orientation:=xlTopToBottom
  1742.     ActiveWindow.SmallScroll Down:=3
  1743.    
  1744.    
  1745. 'Îáðàõóíîê ê³ëüêîñò³ êàòåãîð³é
  1746. Cells.Find(What:="Category_eng", After:=ActiveCell, LookIn:=xlFormulas, _
  1747. LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  1748. MatchCase:=False, SearchFormat:=False).Activate
  1749.  
  1750. For i = 1 To 1048560
  1751.     If ActiveCell.Offset(i, 0).Value = "" Then Exit For
  1752. Next i
  1753.  
  1754. cat_number_other = i - 2
  1755.  
  1756.    
  1757. 'Small cstegories and other
  1758.  
  1759. On Error Resume Next
  1760.        
  1761. ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1762. "Category_eng").PivotItems("Small Category").Position = cat_number_other
  1763. ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1764. "Category_eng").PivotItems("Other").Position = cat_number_other
  1765.        
  1766. 'Êîï³þºìî íà ³íøèé ëèñò
  1767.  
  1768. Range(Cells(8, 1), Cells(7 + cat_number_other, 3)).Select
  1769. Selection.Copy
  1770. Sheets("Òåìïëåéò").Select
  1771. Cells(5, col_start).Select
  1772. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  1773. :=False, Transpose:=False
  1774. Range("D11").Select
  1775.    
  1776.    
  1777. Sheets("Ëèñò1").Select
  1778.    
  1779. 'Äî ïîïåðåäíüîãî ì³ñÿöÿ
  1780.  
  1781.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1782.         "Month")
  1783.         .Orientation = xlColumnField
  1784.         .Position = 2
  1785.     End With
  1786.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1787.         "Year")
  1788.         .Orientation = xlPageField
  1789.         .Position = 1
  1790.     End With
  1791.  
  1792.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Year") _
  1793.         .CurrentPage = "(All)"
  1794.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1795.         "Year")
  1796.         .PivotItems(Year_prev).Visible = False
  1797.     End With
  1798.    
  1799. For i = 1 To Months_number - 2
  1800.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1801.         "Month")
  1802.         .PivotItems(months_a(i)).Visible = False
  1803.     End With
  1804.        
  1805. Next i
  1806.      
  1807. 'Copy/Paste
  1808. Cells.Find(What:=Month, After:=ActiveCell, LookIn:=xlFormulas, _
  1809. LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  1810. MatchCase:=False, SearchFormat:=False).Activate
  1811. c = ActiveCell.Column
  1812. If c <= 1 Then c = enter1
  1813. For f = 1 To 25
  1814.     If Cells(ActiveCell.Row, f).Value = Month_prev Then
  1815.         c1 = f
  1816.         Exit For
  1817.     End If
  1818. Next f
  1819. If c1 <= 1 Then c1 = enter2
  1820. For i = 1 To cat_number_other
  1821.     cat = Worksheets(3).Cells(4 + i, col_start).Value
  1822.    
  1823.     For j = 1 To cat_number_other
  1824.         If Cells(7 + j, 1).Value = cat Then
  1825.             Cells(7 + j, c).Select
  1826.             Selection.Copy
  1827.             Sheets("Òåìïëåéò").Select
  1828.             Cells(4 + i, col_start + 4).Select
  1829.             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  1830.             :=False, Transpose:=False
  1831.             Sheets("Ëèñò1").Select
  1832.            
  1833.             Cells(7 + j, c1).Select
  1834.             Selection.Copy
  1835.             Sheets("Òåìïëåéò").Select
  1836.             Cells(4 + i, col_start + 3).Select
  1837.             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  1838.             :=False, Transpose:=False
  1839.             Sheets("Ëèñò1").Select
  1840.         End If
  1841.     Next j
  1842. Next i
  1843.  
  1844. 'Î÷èùåííÿ çâåäåíî¿
  1845.  
  1846.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  1847.         ).CurrentPage = "(All)"
  1848.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1849.         "media")
  1850.         '.PivotItems("################").Visible = True
  1851.         .PivotItems("Internet").Visible = True
  1852.         .PivotItems("Outdoor").Visible = True
  1853.         .PivotItems("Press").Visible = True
  1854.         .PivotItems("Radio").Visible = True
  1855.         .PivotItems("TV").Visible = True
  1856.     End With
  1857.    
  1858.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Year") _
  1859.         .CurrentPage = "(All)"
  1860.     For b = 2009 To Year - 2
  1861.      
  1862.    With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1863.         "Year")
  1864.         .PivotItems(CStr(b)).Visible = True
  1865.     End With
  1866.    
  1867.     Next b
  1868.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1869.         "Year")
  1870.         '.PivotItems("2014").Visible = True
  1871.         .PivotItems(Year_prev).Visible = True
  1872.     End With
  1873.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1874.         "Month")
  1875.         '.PivotItems("################").Visible = True
  1876.          For k = 1 To 12
  1877.         .PivotItems(months_a(k)).Visible = True
  1878.         Next k
  1879.     End With
  1880.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1881.         "Category_eng")
  1882.         '.PivotItems("################").Visible = True
  1883.         .PivotItems("Advertising").Visible = True
  1884.         .PivotItems("Mass Media").Visible = True
  1885.         .PivotItems("Social").Visible = True
  1886.     End With
  1887.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Month" _
  1888.         ).Orientation = xlHidden
  1889.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1890.         "Êîëè÷åñòâî ïî ïîëþ Net_AdCoal_UAH").Orientation = xlHidden
  1891.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  1892.         ).Orientation = xlHidden
  1893.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1894.         "Spot_Type").Orientation = xlHidden
  1895.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Year") _
  1896.         .Orientation = xlHidden
  1897.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1898.         "Category_eng").Orientation = xlHidden
  1899.     Rows("1:2").Select
  1900.     Selection.delete Shift:=xlUp
  1901.     Range("A4").Select
  1902.    
  1903.  
  1904. End Sub
  1905.  
  1906. Sub Other_advertisers(ByVal media As String)
  1907. 'Advertisers
  1908.  
  1909. If media = "Outdoor" Then
  1910.     col_start = 48
  1911. ElseIf media = "Press" Then
  1912.     col_start = 61
  1913. ElseIf media = "Radio" Then
  1914.     col_start = 74
  1915. End If
  1916.  
  1917. 'Filters
  1918.  
  1919.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").AddDataField _
  1920.         ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1921.         "Net_AdCoal_UAH"), "Êîëè÷åñòâî ïî ïîëþ Net_AdCoal_UAH", xlCount
  1922.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1923.         "Category_eng")
  1924.         .Orientation = xlPageField
  1925.         .Position = 1
  1926.     End With
  1927.    
  1928.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1929.         "Good_Advertiser")
  1930.         .Orientation = xlRowField
  1931.         .Position = 1
  1932.     End With
  1933.    
  1934.    
  1935.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1936.         "media")
  1937.         .Orientation = xlPageField
  1938.         .Position = 1
  1939.     End With
  1940.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1941.         "Month")
  1942.         .Orientation = xlPageField
  1943.         .Position = 1
  1944.     End With
  1945.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1946.         "Year")
  1947.         .Orientation = xlColumnField
  1948.         .Position = 1
  1949.     End With
  1950.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1951.         "Spot_Type")
  1952.         .Orientation = xlPageField
  1953.         .Position = 2
  1954.     End With
  1955.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1956.         "Category_eng").CurrentPage = "(All)"
  1957.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1958.         "Category_eng")
  1959.         '.PivotItems("################").Visible = False
  1960.         .PivotItems("Advertising").Visible = False
  1961.         .PivotItems("Mass Media").Visible = False
  1962.         .PivotItems("Social").Visible = False
  1963.     End With
  1964.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1965.         "Category_eng").EnableMultiplePageItems = True
  1966.    
  1967.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  1968.         ).CurrentPage = "(All)"
  1969.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1970.         "media")
  1971.         '.PivotItems("################").Visible = False
  1972.         .PivotItems("Internet").Visible = False
  1973.         .PivotItems("Outdoor").Visible = False
  1974.         .PivotItems("Press").Visible = False
  1975.         .PivotItems("Radio").Visible = False
  1976.         .PivotItems(media).Visible = True
  1977.         .PivotItems("TV").Visible = False
  1978.     End With
  1979.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  1980.         ).EnableMultiplePageItems = True
  1981.    
  1982.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1983.         "Spot_Type").EnableMultiplePageItems = True
  1984.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Month" _
  1985.         ).CurrentPage = "(All)"
  1986.        
  1987.        
  1988. 'Ïåð³îä äî ïåð³îäó ïîïåðåäíüîãî ðîêó
  1989.  
  1990.     For b = 2009 To Year - 2
  1991.      
  1992.    With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  1993.         "Year")
  1994.         .PivotItems(CStr(b)).Visible = False
  1995.     End With
  1996.    
  1997.     Next b
  1998.    
  1999.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  2000.         "Month")
  2001.         '.PivotItems("################").Visible = False
  2002.         .PivotItems("Apr").Visible = False
  2003.         .PivotItems("Aug").Visible = False
  2004.         .PivotItems("Feb").Visible = False
  2005.         .PivotItems("Jul").Visible = False
  2006.         .PivotItems("Jun").Visible = False
  2007.         .PivotItems("Mar").Visible = False
  2008.         .PivotItems("May").Visible = False
  2009.         .PivotItems("Nov").Visible = False
  2010.         .PivotItems("Oct").Visible = False
  2011.         .PivotItems("Sep").Visible = False
  2012.         .PivotItems("Jan").Visible = False
  2013.        
  2014.         If Months_number <> 12 Then
  2015.             For i = 1 To Months_number
  2016.                 .PivotItems(months_a(i)).Visible = True
  2017.             Next i
  2018.                 .PivotItems("Dec").Visible = False
  2019.         Else
  2020.         For i = 1 To Months_number - 1
  2021.                 .PivotItems(months_a(i)).Visible = True
  2022.             Next i
  2023.         End If
  2024.        
  2025.     End With
  2026.    
  2027.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Month" _
  2028.         ).EnableMultiplePageItems = True
  2029.    
  2030.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  2031.         "Êîëè÷åñòâî ïî ïîëþ Net_AdCoal_UAH").Function = xlSum
  2032.    
  2033.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  2034.         "Good_Advertiser").AutoSort xlDescending, "Ñóììà ïî ïîëþ Net_AdCoal_UAH", _
  2035.         ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotColumnAxis. _
  2036.         PivotLines(3), 1
  2037.    
  2038.     ActiveSheet.Range("$A$15").sort Key1:="R15C3", Order1:=xlDescending, Type _
  2039.         :=xlSortValues, OrderCustom:=1, Orientation:=xlTopToBottom
  2040.     ActiveWindow.SmallScroll Down:=3
  2041.    
  2042.    
  2043. 'Îáðàõóíîê ê³ëüêîñò³ ðåêëàìîäàâö³â
  2044. Cells.Find(What:="Good_Advertiser", After:=ActiveCell, LookIn:=xlFormulas, _
  2045. LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  2046. MatchCase:=False, SearchFormat:=False).Activate
  2047.  
  2048. For i = 1 To 1048560
  2049.     If ActiveCell.Offset(i, 0).Value = "" Then Exit For
  2050. Next i
  2051.  
  2052. adv_number_other = i - 2
  2053.  
  2054.    
  2055. 'Small cstegories and other
  2056.  
  2057. On Error Resume Next
  2058.        
  2059. ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  2060. "Good_Advertiser").PivotItems("Small advertiser").Position = adv_number_other
  2061. ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  2062. "Good_Advertiser").PivotItems("Other").Position = adv_number_other
  2063.        
  2064. 'Êîï³þºìî íà ³íøèé ëèñò
  2065.  
  2066. Range(Cells(8, 1), Cells(7 + adv_number_other, 3)).Select
  2067. Selection.Copy
  2068. Sheets("Òåìïëåéò").Select
  2069. Cells(5, col_start).Select
  2070. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  2071. :=False, Transpose:=False
  2072. Range("D11").Select
  2073.    
  2074.    
  2075. Sheets("Ëèñò1").Select
  2076.    
  2077. 'Äî ïîïåðåäíüîãî ì³ñÿöÿ
  2078.  
  2079.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  2080.         "Month")
  2081.         .Orientation = xlColumnField
  2082.         .Position = 2
  2083.     End With
  2084.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  2085.         "Year")
  2086.         .Orientation = xlPageField
  2087.         .Position = 1
  2088.     End With
  2089.  
  2090.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Year") _
  2091.         .CurrentPage = "(All)"
  2092.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  2093.         "Year")
  2094.         .PivotItems(Year_prev).Visible = False
  2095.     End With
  2096.    
  2097. For i = 1 To Months_number - 2
  2098.       With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  2099.         "Month")
  2100.         .PivotItems(months_a(i)).Visible = False
  2101.         End With
  2102.        
  2103. Next i
  2104.      
  2105. 'Copy/Paste
  2106.  Cells.Find(What:=Month, After:=ActiveCell, LookIn:=xlFormulas, _
  2107. LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  2108. MatchCase:=False, SearchFormat:=False).Activate
  2109. c = ActiveCell.Column
  2110. If c = 1 Then c = enter1
  2111. For f = 1 To 25
  2112.     If Cells(ActiveCell.Row, f).Value = Month_prev Then
  2113.         c1 = f
  2114.         Exit For
  2115.     End If
  2116. Next f
  2117. If c1 = c Or c1 <= 1 Then c1 = enter2
  2118. For i = 1 To adv_number_other
  2119.     adv = Worksheets(3).Cells(4 + i, col_start).Value
  2120.    
  2121.     For j = 1 To adv_number_other
  2122.         If Cells(7 + j, 1).Value = adv Then
  2123.             Cells(7 + j, c).Select
  2124.             Selection.Copy
  2125.             Sheets("Òåìïëåéò").Select
  2126.             Cells(4 + i, col_start + 4).Select
  2127.             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  2128.             :=False, Transpose:=False
  2129.             Sheets("Ëèñò1").Select
  2130.            
  2131.             Cells(7 + j, c1).Select
  2132.             Selection.Copy
  2133.             Sheets("Òåìïëåéò").Select
  2134.             Cells(4 + i, col_start + 3).Select
  2135.             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  2136.             :=False, Transpose:=False
  2137.             Sheets("Ëèñò1").Select
  2138.         End If
  2139.     Next j
  2140. Next i
  2141.  
  2142. 'Î÷èùåííÿ çâåäåíî¿
  2143.  
  2144.  ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  2145.         ).CurrentPage = "(All)"
  2146.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  2147.         "media")
  2148.         '.PivotItems("################").Visible = True
  2149.         .PivotItems("Internet").Visible = True
  2150.         .PivotItems("Outdoor").Visible = True
  2151.         .PivotItems("Press").Visible = True
  2152.         .PivotItems("Radio").Visible = True
  2153.         .PivotItems("TV").Visible = True
  2154.     End With
  2155.    
  2156.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Year") _
  2157.         .CurrentPage = "(All)"
  2158.     For b = 2009 To Year - 2
  2159.      
  2160.    With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  2161.         "Year")
  2162.         .PivotItems(CStr(b)).Visible = True
  2163.     End With
  2164.    
  2165.     Next b
  2166.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  2167.         "Year")
  2168.         '.PivotItems("2014").Visible = True
  2169.         .PivotItems(Year_prev).Visible = True
  2170.     End With
  2171.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  2172.         "Month")
  2173.         '.PivotItems("################").Visible = True
  2174.         For k = 1 To 12
  2175.         .PivotItems(months_a(k)).Visible = True
  2176.         Next k
  2177.     End With
  2178.     With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  2179.         "Category_eng")
  2180.         '.PivotItems("################").Visible = True
  2181.         .PivotItems("Advertising").Visible = True
  2182.         .PivotItems("Mass Media").Visible = True
  2183.         .PivotItems("Social").Visible = True
  2184.     End With
  2185.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Month" _
  2186.         ).Orientation = xlHidden
  2187.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  2188.         "Êîëè÷åñòâî ïî ïîëþ Net_AdCoal_UAH").Orientation = xlHidden
  2189.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("media" _
  2190.         ).Orientation = xlHidden
  2191.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  2192.         "Spot_Type").Orientation = xlHidden
  2193.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields("Year") _
  2194.         .Orientation = xlHidden
  2195.     ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  2196.         "Category_eng").Orientation = xlHidden
  2197.         ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà1").PivotFields( _
  2198.         "Good_Advertiser").Orientation = xlHidden
  2199.     Rows("1:2").Select
  2200.     Selection.delete Shift:=xlUp
  2201.     Range("A4").Select
  2202.    
  2203.     End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!