Run-time error 1004 method location object chart failed

Question

I have a large spreadsheet with 5 tabs, one for each day of the work week (Monday through Friday).   The first column is server name, next is application identifier, then time of day, then 4 columns of data.   I have a macro that cycles through each tab and produces a graph by server by application identifier.    After creating each chart I do "ActiveChart.Location xlLocationAsNewSheet, tabString".   The intent is to move each chart to its own chart tab.  This is where the failure occurs.   But the problem isn't that simple.

When I wrote this macro I used an existing macro that does something similar with a spreadsheet with about a dozen data tabs.  That original macro works fine.   For the new macro I started by only coding it to process the first tab.   That worked fine.  When I duplicated that code to process the second tab, I get the run-time error on the first graph.    I moved the code for the second tab into its own subroutine, then executed the code for the first tab (chart tabs created), followed by running the code for the second tab, and I get the same failure.   If I try to run the code for the 2nd tab first (chart tabs created) and then the 1st tab, I get the run-time error on the first graph in the 1st tab..   That last attempt tells me there is not an error in my coding. 

The original spreadsheet with 5 tabs of data is just under 1.5MB in size.   Adding the chart graphs for the first tab adds about 300KB to the size.   Assuming the same for the second tab, that throws the total size over 2MB.   Is there a limit to the size of a spreadsheet?   That spreadsheet with the dozen tabs with the working macro is less than 1MB with the chart tabs added.

Answer 1

Thank you very much for your insight and attempt to help.   I decided that I needed to include my code here so that others could see exactly what I was doing, but before I could do that I needed to "clean" my code so that it didn't have proprietary information.   Sometimes you need to look at something from a totally different angle to see the obvious.   I found that my code for processing the next data tab (Tuesday) was creating chart tab names with exactly the same tab names as for the Monday data tab.   I was creating Chart titles that were unique but the tab name has a smaller size limit.   Unique tab names solves the problem.

Answer 2

No, I am selecting ActiveChart each time.

But that is the issue!

In the old Office products macros worked when they use SELECT, SELECTION, ACTIVE(whatever), but it seems that in newer products that "processes" runs more and more asynchronous.

So many Users in this forums reports that macros did not work anymore, in 99% this macro use such commands and when the macro is rewritten in a way that these commands are not longer used, the macro works again.

And in 99.99% there is no need to use them! See the code below how to create a chart and get the object pointer directly.

Andreas.

Sub Example_CreateChartSheet()

  'Creates a chart as standalone sheet

  Dim C As Chart

  Set C = Charts.Add(After:=Sheets(Sheets.Count))

End Sub

Sub Example_CreateChartInWorksheet()

  'Creates a chart in a worksheet

  Dim Ws As Worksheet

  Dim R As Range

  Dim Co As ChartObject

  Dim C As Chart

  Dim S As Series

  'Get the worksheet

  Set Ws = Worksheets(Worksheets.Count)

  'Get the cells where the chart should appear

  Set R = Ws.Range("B2:D8")

  With R

    'Create the chartobject

    Set Co = Ws.ChartObjects.Add(.Left, .Top, .Width, .Height)

  End With

  'Get the chart

  Set C = Co.Chart

End Sub

Answer 3

No, I am selecting ActiveChart each time.  Otherwise my process would never work as it does on my other spreadsheets.  I split my spreadsheet into 5 smaller (400KB) ones and my VBA is working fine now.

This has to involve the size in megabytes of the spreadsheet.  There must be a 2MB limit but I can't find that documented.

Answer 4

Re:  chart.location

My guess... 

  you are not selecting each chart to make each one the "ActiveChart".

You could use code similar to...

  For N = 1 to 5

    Worksheets(N).ChartObjects(1).Chart.Location xlLocationAsNewSheet, tabString

  Next

'---

Jim Cone

Portland, Oregon USA

free & commercial excel programs  (n/a xl2013)

http://jmp.sh/K95N3ee