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

JONTYH26

New Member
Joined
Dec 14, 2018
Messages
6
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.

Code:
'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?
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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)

1A BCDEFGHIJK
2Schools Spending Analysis 2015/16-2018/191003
3School Number10031100
4School Name££££1102
5Staffing Percentages2015/162016/172017/182018/192006
6Teaching183,106206,895189,233201,4942009
7Support Staff256,030241,759208,051233,0072012
8Admin & Clerical27,33524,97125,36226,2472013
9Premises & Grounds53,81858,42061,23552,1982014
10Energy & Water10,5699,2878,63410,5502015
11Curriculum etc19,91021,38232,01527,9022019
12Supplies & Services73,56177,89275,08264,7732020
132025
14Schools Spending Analysis 2015/16-2018/19 -2027
152029
16
Teaching StaffSupport StaffAdmin & Clerical StaffPremises & GroundsEnergy & WaterCurriculum Based ExpenditureSupplies & Services

172015/1629.3%41.0%4.4%8.6%1.7%3.2%11.8%

182016/1732.3%37.7%3.9%9.1%1.4%3.3%12.2%

192017/1831.6%34.7%4.2%10.2%1.4%5.3%12.5%

202018/1932.7%37.8%4.3%8.5%1.7%4.5%10.5%

21










<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top