Macro keeps creating a charts for interger 2 rather than looping down a list.

[JONTYH26]

So I have a basic Macro to select some data and create a chart - this works perfectly.

The problem I have is that I need to create 78 of these charts.

I am trying to get Excel to update the chart data for each estblishment based on a single reference cell that drive a host of VLOOKUP and INDEX Arguments. When it has created the first chart and heads back to do the next one, it doesn't change the single value reference and keeps creating the same chart oer and over.

I am testing with only 4 references at first but want to rollout to 78 eventually.

'Application.ScreenUpdating = False
' This will eventually be 2 to 79
  For I = 2 To 5
  
 


    Range("K" & I).Copy


   ActiveSheet.Range("b3").PasteSpecial Paste:=xlPasteValues


'this sub works perfectly
   Call createchart


   On Error Resume Next
'name of the sheet where I want Excel to loop through the list
   ActiveSheet.Range("Chart Data").Activate
  
   
  Next I


  Application.CutCopyMode = False
  'Application.ScreenUpdating = True


End Sub

The issue is that it keeps creating charts for Interger 2

Help please?

 

[JONTYH26]

I need Excel to do the following
1. Update B3 with the 1st number on the Right hand side (K2)
2. Create a bar chart of the data in red.
3. copy the chart to a new sheet.
4. Save as PDF
5. Go back to the original sheet
6. Do the same for the next number in the list on the right (K3)

[TABLE="class: grid, width: 1237"]
<tbody>[TR]
[TD]1[/TD]
[TD="colspan: 2"]A B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD="align: right"]K[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="colspan: 2"]Schools Spending Analysis 2015/16-2018/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1003[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]School Number[/TD]
[TD="align: right"]1003[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1100[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]School Name[/TD]
[TD][/TD]
[TD]£[/TD]
[TD]£[/TD]
[TD]£[/TD]
[TD]£[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1102[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Staffing Percentages[/TD]
[TD][/TD]
[TD]2015/16[/TD]
[TD]2016/17[/TD]
[TD]2017/18[/TD]
[TD]2018/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2006[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Teaching[/TD]
[TD="align: right"]183,106[/TD]
[TD="align: right"]206,895[/TD]
[TD="align: right"]189,233[/TD]
[TD="align: right"]201,494[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2009[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]Support Staff[/TD]
[TD="align: right"]256,030[/TD]
[TD="align: right"]241,759[/TD]
[TD="align: right"]208,051[/TD]
[TD="align: right"]233,007[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2012[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]Admin & Clerical[/TD]
[TD="align: right"]27,335[/TD]
[TD="align: right"]24,971[/TD]
[TD="align: right"]25,362[/TD]
[TD="align: right"]26,247[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2013[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]Premises & Grounds[/TD]
[TD="align: right"]53,818[/TD]
[TD="align: right"]58,420[/TD]
[TD="align: right"]61,235[/TD]
[TD="align: right"]52,198[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2014[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]Energy & Water[/TD]
[TD="align: right"]10,569[/TD]
[TD="align: right"]9,287[/TD]
[TD="align: right"]8,634[/TD]
[TD="align: right"]10,550[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2015[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]Curriculum etc[/TD]
[TD="align: right"]19,910[/TD]
[TD="align: right"]21,382[/TD]
[TD="align: right"]32,015[/TD]
[TD="align: right"]27,902[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]Supplies & Services[/TD]
[TD="align: right"]73,561[/TD]
[TD="align: right"]77,892[/TD]
[TD="align: right"]75,082[/TD]
[TD="align: right"]64,773[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2020[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2025[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Schools Spending Analysis 2015/16-2018/19 -[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2027[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2029[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]
[/TD]
[TD]Teaching Staff[/TD]
[TD]Support Staff[/TD]
[TD]Admin & Clerical Staff[/TD]
[TD]Premises & Grounds[/TD]
[TD]Energy & Water[/TD]
[TD]Curriculum Based Expenditure[/TD]
[TD]Supplies & Services[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]2015/16[/TD]
[TD="align: right"]29.3%[/TD]
[TD="align: right"]41.0%[/TD]
[TD="align: right"]4.4%[/TD]
[TD="align: right"]8.6%[/TD]
[TD="align: right"]1.7%[/TD]
[TD="align: right"]3.2%[/TD]
[TD="align: right"]11.8%[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]2016/17[/TD]
[TD="align: right"]32.3%[/TD]
[TD="align: right"]37.7%[/TD]
[TD="align: right"]3.9%[/TD]
[TD="align: right"]9.1%[/TD]
[TD="align: right"]1.4%[/TD]
[TD="align: right"]3.3%[/TD]
[TD="align: right"]12.2%[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]2017/18[/TD]
[TD="align: right"]31.6%[/TD]
[TD="align: right"]34.7%[/TD]
[TD="align: right"]4.2%[/TD]
[TD="align: right"]10.2%[/TD]
[TD="align: right"]1.4%[/TD]
[TD="align: right"]5.3%[/TD]
[TD="align: right"]12.5%[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]2018/19[/TD]
[TD="align: right"]32.7%[/TD]
[TD="align: right"]37.8%[/TD]
[TD="align: right"]4.3%[/TD]
[TD="align: right"]8.5%[/TD]
[TD="align: right"]1.7%[/TD]
[TD="align: right"]4.5%[/TD]
[TD="align: right"]10.5%[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]