Poly’s method is not ideal, since I’m getting a different excel file each time and I’d prefer to keep the graphs in those files. But it’s a good suggestion because if it runs faster with the graphs pre-created, then it’ll be worth it. I’ll try that at some point. I’ll just have to copy-paste the new data into the template and then update the ranges of data that each graph refers to.
Dim newChartObj As ChartObject
Set newChartObj = ActiveSheet.ChartObjects.Add(endRange.Left, endRange.Offset(2, 0).Top, 400, 300)
It’s specifically the second line where the program is hanging - it can spend 60-90 seconds on this, just creating the graph object. At this point the graph object doesn’t even contain any data - that’s added in the next few lines and doesn’t take any time.
The slowness does seem to be related to the size of spreadsheet. If I delete a bunch of columns, reducing the spreadsheet to around 7Mb, it takes 5-10 seconds to create the graph. Still slower than I’d have hoped for but much acceptable. But ultimately, I’d prefer to keep these columns. All of this slowness makes no sense to me because if I create the graph manually, it happens immediately, even if I haven’t deleted any columns.
@Vorian - there aren’t “a lot” of calculations (0 in fact), so that’s not the issue. I did try with calculations set to manual just in case that made a difference but it didn’t. I also tried setting screenupdating to false without any effect.