API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Dec 12th, 2019
131
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
VBScript 10.54 KB | None | 0 0
raw download clone embed print report
  1. Sub Macro1()
  2. '
  3. ' Macro1 Macro
  4. '
  5.  
  6. '
  7.  
  8. s = ActiveSheet.Name
  9.  
  10.     Range("AQ1").Select
  11.     ActiveCell.FormulaR1C1 = "ROE"
  12.     Range("AQ1").Select
  13.     Selection.ClearContents
  14.     Range("A29").Select
  15.     ActiveCell.FormulaR1C1 = "ROE"
  16.     Range("B29").Select
  17.     Application.CutCopyMode = False
  18.     ActiveCell.FormulaR1C1 = "=R[-11]C/R[-17]C"
  19.     Range("B29").Select
  20.     Selection.AutoFill Destination:=Range("B29:AP29"), Type:=xlFillDefault
  21.     Range("B29:AP29").Select
  22.     ActiveCell.FormulaR1C1 = "=IFERROR(R[-11]C/R[-17]C,""Unavailable"")"
  23.     Range("B29").Select
  24.     Selection.AutoFill Destination:=Range("B29:AP29"), Type:=xlFillDefault
  25.     Range("B29:AP29").Select
  26.     Range("B29,G29,L29,Q29,V29,AA29:AP29").Select
  27.     Range("AA29").Activate
  28.     ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
  29.     ActiveChart.SetSourceData Source:=Range( _
  30.         "!$B$29,ANI!$G$29,ANI!$L$29,ANI!$Q$29,ANI!$V$29,ANI!$AA$29:$AP$29")
  31.     ActiveChart.ChartType = xlLine
  32.     ActiveChart.Axes(xlCategory).Select
  33.     ActiveChart.ChartArea.Select
  34.     Range("B1,G1,L1,Q1,V1,AA1:AD1,AE1:AJ1,AK1:AN1,AO1:AP1,AP29").Select
  35.     Range("AP29").Activate
  36.     Range(Selection, Selection.End(xlToLeft)).Select
  37.     Selection.End(xlToRight).Select
  38.     Range(Selection, Selection.End(xlToLeft)).Select
  39.     Range("WVS26").Select
  40.     Selection.End(xlToLeft).Select
  41.     Range("B1").Select
  42.     ActiveCell.FormulaR1C1 = "2019"
  43.     Range("B1,B29,G1,G29,L1,L29,Q1,Q29,V1,V29,AA1,AA29,AB1:AP1,AB29:AP29").Select
  44.     Range("AB29").Activate
  45.     ActiveSheet.Shapes.AddChart2(227, xlLine).Select
  46.     ActiveChart.SetSourceData Source:=Range( _
  47.         s & "!$B$1," s & !$B$29,ANI!$G$1,ANI!$G$29,ANI!$L$1,ANI!$L$29,ANI!$Q$1,ANI!$Q$29,ANI!$V$1,ANI!$V$29,ANI!$AA$1,ANI!$AA$29,ANI!$AB$1:$AP$1,ANI!$AB$29:$AP$29" _
  48.        )
  49.    ActiveChart.Axes(xlCategory).Select
  50.    ActiveChart.Axes(xlCategory).ReversePlotOrder = True
  51.    ActiveChart.ChartTitle.Select
  52.    ActiveChart.ChartTitle.Text = "ROE"
  53.    ActiveChart.ChartArea.Select
  54.    Range("A30").Select
  55.    ActiveCell.FormulaR1C1 = "ROA"
  56.    Range("B30").Select
  57.    Application.CutCopyMode = False
  58.    ActiveCell.FormulaR1C1 = "=R[-12]C/R[-24]C"
  59.    Range("B30").Select
  60.    ActiveCell.FormulaR1C1 = "=IFERROR(R[-12]C/R[-24]C,""Unavailable"")"
  61.    Range("B30").Select
  62.    Selection.AutoFill Destination:=Range("B30:AP30"), Type:=xlFillDefault
  63.    Range("B30:AP30").Select
  64.    Range("B1,B30,G1,G30,L1,L30,Q1,Q30,V1,V30,AA1,AA30").Select
  65.    Range("AA30").Activate
  66.    ActiveWindow.Zoom = 89
  67.    ActiveWindow.Zoom = 57
  68.    Range("B1,B30,G1,G30,L1,L30,Q1,Q30,V1,V30,AA1,AA30,AB1:AP1,AB30:AP30").Select
  69.    Range("AB30").Activate
  70.    ActiveSheet.Shapes.AddChart2(227, xlLine).Select
  71.    ActiveChart.SetSourceData Source:=Range( _
  72.        "ANI!$B$1,ANI!$B$30,ANI!$G$1,ANI!$G$30,ANI!$L$1,ANI!$L$30,ANI!$Q$1,ANI!$Q$30,ANI!$V$1,ANI!$V$30,ANI!$AA$1,ANI!$AA$30,ANI!$AB$1:$AP$1,ANI!$AB$30:$AP$30" _
  73.        )
  74.    ActiveChart.Axes(xlCategory).Select
  75.    ActiveChart.Axes(xlCategory).ReversePlotOrder = True
  76.    ActiveChart.ChartTitle.Select
  77.    ActiveChart.ChartTitle.Text = "ROA"
  78.    ActiveChart.ChartArea.Select
  79.    Range("A31").Select
  80.    ActiveCell.FormulaR1C1 = "Debt to Equity"
  81.    Range("B31").Select
  82.    Application.CutCopyMode = False
  83.    ActiveCell.FormulaR1C1 = "=R[-21]C/R[-19]C"
  84.    Range("A32").Select
  85.    ActiveCell.FormulaR1C1 = "Profitability Ratio"
  86.    Range("A32").Select
  87.    ActiveCell.FormulaR1C1 = "Profitability Ratios"
  88.    Range("A33").Select
  89.    ActiveCell.FormulaR1C1 = "Profit Margin"
  90.    Range("B33").Select
  91.    ActiveCell.FormulaR1C1 = "=IFERROR(R[-15]C/R[-19]C,""Unavailable"")"
  92.    Range("B33").Select
  93.    Selection.AutoFill Destination:=Range("B33:AP33"), Type:=xlFillDefault
  94.    Range("B33:AP33").Select
  95.    Range("B2").Select
  96.    ActiveWindow.FreezePanes = True
  97.    Range( _
  98.        "B2,B1,B33,G1,G33,L1,L33,Q1,Q33,V1,V33,AA1:AG1,AH1:AK1,AL1:AO1,AP1,AA33:AG33,AH33:AL33,AM33:AP33" _
  99.        ).Select
  100.    Range("AM33").Activate
  101.    ActiveSheet.Shapes.AddChart2(227, xlLine).Select
  102.    ActiveChart.SetSourceData Source:=Range( _
  103.        "ANI!$B$2,ANI!$B$1,ANI!$B$33,ANI!$G$1,ANI!$G$33,ANI!$L$1,ANI!$L$33,ANI!$Q$1,ANI!$Q$33,ANI!$V$1,ANI!$V$33,ANI!$AA$1:$AG$1,ANI!$AH$1:$AK$1,ANI!$AL$1:$AO$1,ANI!$AP$1,ANI!$AA$33:$AG$33,ANI!$AH$33:$AL$33,ANI!$AM$33:$AP$33" _
  104.        )
  105.    ActiveChart.Legend.Select
  106.    ActiveChart.ChartArea.Select
  107.    Range("AQ34").Select
  108.    Selection.ClearContents
  109.    ActiveWindow.Zoom = 67
  110.    Range("B1").Select
  111.    ActiveCell.FormulaR1C1 = "2019"
  112.    Range( _
  113.        "B1,G1,L1,Q1,V1,AA1:AK1,AP1,AL1:AP1,B33,G33,L33,Q33,V33,AA33:AJ33,AK33:AP33"). _
  114.        Select
  115.    Range("AK33").Activate
  116.    ActiveSheet.Shapes.AddChart2(227, xlLine).Select
  117.    ActiveChart.SetSourceData Source:=Range( _
  118.        "ANI!$B$1,ANI!$G$1,ANI!$L$1,ANI!$Q$1,ANI!$V$1,ANI!$AA$1:$AK$1,ANI!$AP$1,ANI!$AL$1:$AP$1,ANI!$B$33,ANI!$G$33,ANI!$L$33,ANI!$Q$33,ANI!$V$33,ANI!$AA$33:$AJ$33,ANI!$AK$33:$AP$33" _
  119.        )
  120.    ActiveChart.Legend.Select
  121.    ActiveWindow.Zoom = 107
  122.    ActiveChart.Legend.LegendEntries(10).Select
  123.    ActiveChart.FullSeriesCollection(14).Select
  124.    ActiveChart.FullSeriesCollection(14).Points(9).Select
  125.    ActiveChart.ChartArea.Select
  126.    ActiveChart.Legend.Select
  127.    ActiveWindow.Zoom = 12
  128.    ActiveWindow.Zoom = 50
  129.    ActiveChart.ChartArea.Select
  130.    ActiveSheet.Shapes.AddChart2(227, xlLineStacked).Select
  131.    ActiveChart.SetSourceData Source:=Range( _
  132.        "ANI!$B$1,ANI!$G$1,ANI!$L$1,ANI!$Q$1,ANI!$V$1,ANI!$AA$1:$AK$1,ANI!$AP$1,ANI!$AL$1:$AP$1,ANI!$B$33,ANI!$G$33,ANI!$L$33,ANI!$Q$33,ANI!$V$33,ANI!$AA$33:$AJ$33,ANI!$AK$33:$AP$33" _
  133.        )
  134.    Selection.ClearContents
  135.    Range("AA39").Select
  136.    ActiveWindow.Zoom = 100
  137.    Range("B33,G33,L33,Q33,V33,AA33:AP33").Select
  138.    Range("AA33").Activate
  139.    ActiveSheet.Shapes.AddChart2(227, xlLine).Select
  140.    ActiveChart.SetSourceData Source:=Range( _
  141.        "ANI!$B$33,ANI!$G$33,ANI!$L$33,ANI!$Q$33,ANI!$V$33,ANI!$AA$33:$AP$33")
  142.    ActiveChart.Axes(xlCategory).Select
  143.    ActiveChart.Axes(xlCategory).ReversePlotOrder = True
  144.    ActiveChart.Axes(xlCategory).Select
  145.    ActiveChart.Axes(xlCategory).CategoryType = xlTimeScale
  146.    ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale
  147.    ActiveChart.Axes(xlCategory).CategoryType = xlTimeScale
  148.    ActiveChart.Axes(xlCategory).TickLabelSpacing = 1
  149.    ActiveChart.Axes(xlCategory).TickLabelSpacingIsAuto = True
  150.    ActiveChart.ChartArea.Select
  151.    Range("B2").Select
  152.    ActiveWindow.FreezePanes = False
  153.    Range("B33").Select
  154.    Range("B33,G33").Select
  155.    Range("G33").Activate
  156.    Range("B33,G33,L33").Select
  157.    Range("L33").Activate
  158.    Range("B33,G33,L33,Q34").Select
  159.    Range("Q34").Activate
  160.    Range("B33,G33,L33,Q34,Q33").Select
  161.    Range("Q33").Activate
  162.    Range("B33,G33,L33,Q33").Select
  163.    Range("Q33").Activate
  164.    Range("B33,G33,L33,Q33,V33").Select
  165.    Range("V33").Activate
  166.    Range("B33,G33,L33,Q33,V33,AA33:AP33").Select
  167.    Range("AA33").Activate
  168.    Range("B33,G33,L33,Q33,V33,AA33:AP33,AE1:AP1").Select
  169.    Range("AP1").Activate
  170.    Range("B33,G33,L33,Q33,V33,AA33:AP33,AE1:AP1,AA1:AD1").Select
  171.    Range("AD1").Activate
  172.    Range("B33,G33,L33,Q33,V33,AA33:AP33,AE1:AP1,AA1:AD1,V1").Select
  173.    Range("V1").Activate
  174.    Range("B33,G33,L33,Q33,V33,AA33:AP33,AE1:AP1,AA1:AD1,V1,Q1").Select
  175.    Range("Q1").Activate
  176.    Range("B33,G33,L33,Q33,V33,AA33:AP33,AE1:AP1,AA1:AD1,V1,Q1,L1").Select
  177.    Range("L1").Activate
  178.    Range("B33,G33,L33,Q33,V33,AA33:AP33,AE1:AP1,AA1:AD1,V1,Q1,L1,G1").Select
  179.    Range("G1").Activate
  180.    Range("B1").Select
  181.    Range("B32").Select
  182.    ActiveCell.FormulaR1C1 = "2019"
  183.    Range("G32").Select
  184.    ActiveCell.FormulaR1C1 = "2018"
  185.    Range("L32").Select
  186.    ActiveCell.FormulaR1C1 = "2017"
  187.    Range("Q32").Select
  188.    ActiveCell.FormulaR1C1 = "2016"
  189.    Range("V32").Select
  190.    ActiveCell.FormulaR1C1 = "2015"
  191.    Range("AA32").Select
  192.    ActiveCell.FormulaR1C1 = "2014"
  193.    Range("AB32").Select
  194.    ActiveCell.FormulaR1C1 = "2013"
  195.    Range("AC32").Select
  196.    ActiveCell.FormulaR1C1 = "2012"
  197.    Range("AA32:AC32").Select
  198.    Selection.AutoFill Destination:=Range("AA32:AP32"), Type:=xlFillDefault
  199.    Range("AA32:AP32").Select
  200.    Range("AP33").Select
  201.    Range(Selection, Selection.End(xlUp)).Select
  202.    Range(Selection, Selection.End(xlToLeft)).Select
  203.    Range(Selection, Selection.End(xlToRight)).Select
  204.    Range(Selection, Selection.End(xlToLeft)).Select
  205.    Range("AA32:AP33,V32:V33,Q32:Q33,L32:L33,G32:G33,B32:B33").Select
  206.    Range("B32").Activate
  207.    ActiveSheet.Shapes.AddChart2(227, xlLine).Select
  208.    ActiveChart.SetSourceData Source:=Range( _
  209.        "ANI!$AA$32:$AP$33,ANI!$V$32:$V$33,ANI!$Q$32:$Q$33,ANI!$L$32:$L$33,ANI!$G$32:$G$33,ANI!$B$32:$B$33" _
  210.        )
  211.    ActiveChart.PlotArea.Select
  212.    ActiveChart.FullSeriesCollection(2).Select
  213.    ActiveChart.ChartArea.Select
  214.    Range("B32").Select
  215.    Selection.ClearContents
  216.    Range("B32").Select
  217.    Range(Selection, Selection.End(xlToRight)).Select
  218.    Range(Selection, Selection.End(xlToRight)).Select
  219.    Range(Selection, Selection.End(xlToRight)).Select
  220.    Range(Selection, Selection.End(xlToRight)).Select
  221.    Range(Selection, Selection.End(xlToRight)).Select
  222.    Range(Selection, Selection.End(xlToRight)).Select
  223.    Range(Selection, Selection.End(xlToRight)).Select
  224.    Range(Selection, Selection.End(xlToLeft)).Select
  225.    Selection.ClearContents
  226.    Range("A33").Select
  227.    Range(Selection, Selection.End(xlToRight)).Select
  228.    Selection.Cut
  229.    Rows("31:31").Select
  230.    Selection.Insert Shift:=xlDown
  231.    Range("A33").Select
  232.    Selection.ClearContents
  233.    Range("B31,B1,G1,G31,L31,L1,Q1,Q31,V1,V31,AA1,AA31,AB1:AP1,AB31:AP31").Select
  234.    Range("AB31").Activate
  235.    ActiveSheet.Shapes.AddChart2(227, xlLine).Select
  236.    ActiveChart.SetSourceData Source:=Range( _
  237.        "ANI!$B$31,ANI!$B$1,ANI!$G$1,ANI!$G$31,ANI!$L$31,ANI!$L$1,ANI!$Q$1,ANI!$Q$31,ANI!$V$1,ANI!$V$31,ANI!$AA$1,ANI!$AA$31,ANI!$AB$1:$AP$1,ANI!$AB$31:$AP$31" _
  238.        )
  239.    ActiveChart.Axes(xlCategory).Select
  240.    ActiveChart.Axes(xlCategory).ReversePlotOrder = True
  241.    ActiveChart.ChartTitle.Select
  242.    ActiveChart.ChartTitle.Text = "Net Profit Margin"
  243.    Range("AN15").Select
  244.    ActiveSheet.ChartObjects("Chart 9").Activate
  245.    ActiveSheet.ChartObjects("Chart 9").Activate
  246.    ActiveSheet.ChartObjects("Chart 9").Activate
  247.    Sheets("Historical Prices Graphs").Select
  248.    ActiveWindow.Zoom = 388
  249.    ActiveWindow.Zoom = 200
  250.    ActiveWindow.Zoom = 150
  251.    Sheets("ANI").Select
  252.    Rows("32:32").Select
  253.    Selection.Insert Shift:=xlDown
  254.    Range("A32").Select
  255. 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!