0

I wrote a macro that imports a large CSV data file, organizes the data, and creates charts. The charts are created by:

  • Copy template chart
  • Paste on another tab
  • Rename chart
  • Add data series
  • Repeat for each column of data

The problem I'm having is that when I run the macro normally, it doesn't actually copy the template chart. So there's nothing on the clipboard when it goes to paste, which throws an error. Here's the actual code:

wbGen.Sheets("Dashboard").ChartObjects("Chart 0").Activate
ActiveChart.ChartArea.Copy
wbGen.Sheets("Charts").Activate
ActiveSheet.Cells(iRotor * 16 - 31, iChartA * 7 - 6).Select
ActiveSheet.Paste 'Debug mode points to this line
ActiveSheet.ChartObjects("Chart 0").Activate
ActiveSheet.Shapes("Chart 0").Name = "Chart " & iChart

Here's the rub: if I click Debug, back the cursor up to the first line above, and hit continue, then the code executes perfectly. I can't figure out why it works in debug mode but not normally. I appreciate any help you can provide.

As a side note, my attempts to get rid of the Activate and Select statements haven't been fruitful. Any help I can get in that area would also be appreciated.

Community
  • 1
  • 1
Kes Perron
  • 455
  • 5
  • 12
  • 24
  • what are you trying to do in the line above the Error-line? – Kathara Jan 26 '16 at 14:20
  • What is the value of `iRotor` and `iChartA` when the code doesn't work? – SierraOscar Jan 26 '16 at 14:26
  • Line 4 defines where the chart is pasted. For example, the first iteration (when the code doesn't work) has `iRotor = 2` and `iChartA = 1`. This causes Cell (1,1) to be selected and the chart pasted there. On the next iteration, `iRotor = 2` and `iChartA = 2`, so the next chart is pasted at Cell (1, 8). – Kes Perron Jan 26 '16 at 14:39

2 Answers2

0

Close vbe editor when running that code...

Sheets("Dashboard").ChartObjects("Chart 0").Copy
Sheets("Charts").Select
Sheets("Charts").Cells(iRotor * 16 - 31, iChartA * 7 - 6).Select
Sheets("Charts").PasteSpecial Format:="Microsoft Office Drawing Object", Link:=False, DisplayAsIcon:=False
Herry Markowitz
  • 208
  • 3
  • 15
0

Try something like this:

Dim chtTemplate As Chart
Set chtTemplate = wbGen.Sheets("Dashboard").ChartObjects("Chart 0").Chart
DoEvents
chtTemplate.ChartArea.Copy

or

Dim chtTemplate As ChartObject
Set chtTemplate = wbGen.Sheets("Dashboard").ChartObjects("Chart 0")
DoEvents
chtTemplate.Copy

then proceed.

Jon Peltier
  • 5,895
  • 1
  • 27
  • 27