0

I can no longer select rows after copying a chart in a sheet (I'm using Excel 2003).

These lines (in a 1000+ lines macro)

Sheets("Bilan RDF").Select
Rows(ligne_UDI(1) & ":" & ligne_UDI(1) + 1).Select
ActiveSheet.Paste

works usually fine (there isn't any problem with the ligne_UDI values) but the Rows().Select fails after adding a chart on this sheet

(Traduct from french)
Error 1004
The 'Rows' method of the '_Global' objet failed

I tried using the following line to avoid the select line but it's also failing

ActiveSheet.Paste Destination = Rows(ligne_UDI(1) & ":" & ligne_UDI(1) + 1)

I though it could be the previous copy so I added the following line. It didn't help

Application.CutCopyMode = False

Any idea ?

Tibo
  • 383
  • 5
  • 27
  • [Stop using Select](http://stackoverflow.com/q/10714251/11683) and stop using [`Rows` not qualified by sheet name](http://stackoverflow.com/a/24370404/11683). – GSerg Dec 11 '14 at 08:08
  • @GSerg : Thanks. Did you see the paragraph where I say that avoiding the select line (by using "Paste Destination=...") didn't help ? I still need to select the sheet. Is there a way to avoid selecting (or activating) the sheet for pasting rows ? – Tibo Dec 11 '14 at 20:37
  • What are you copying? Is the range that you are copying the same size as the range where you are pasting? Do you have any merged cells in either range? – TheEngineer Dec 18 '14 at 18:25
  • @TheEngineer : Thanks. I'm copying a single row on a single row, but it isn't the problem as the 'select' is already failing and as the macro works fine if I didn't add the chart. One "solution" seems to be to select a single cells (which unselect the chart) before selecting the row. – Tibo Dec 24 '14 at 10:44
  • As @GSerg stated, you should always avoid using `Select `. In your case, you can just qualify each line with the sheet name. I'll post it as an answer. – TheEngineer Dec 24 '14 at 12:39
  • @Tibo Did the below answer work for you or are you still encountering this problem? – TheEngineer Jan 02 '15 at 15:40
  • @TheEngineer. Thanks, but no, it did not work as `Sheets().Rows().Paste` isn't allowed thus I still need to select the row before the `.Paste` so Excel knows where to paste the graph. – Tibo Jan 19 '15 at 20:19
  • @Tibo I'm not understanding what you are trying to accomplish. You stated earlier that you are copying a single row and now you're saying that you are pasting a graph. What exactly are you copying? – TheEngineer Jan 19 '15 at 20:36
  • @TheEngineer. My bad, sorry ; I got confused myself. ----- I have a (rather big) macro which works fine, until I try to add something like 50 charts in a sheet by copying then pasting a "model chart" from a sheet to another one. The thing is that I need to use a `.Select` in order for Excel to know where to `.Paste` the charts. I mean I thus cannot totally avoid the `.Select` in my macro, which I should otherwise do. – Tibo Jan 21 '15 at 00:26
  • @TheEngineer ----- BUT I'm not sure that the problem is with the `.Select`. I'm thinking it might be rather with the pasting of the chart, which then block the `.Select`. Even if one should avoid using `.Select` for performance... I don't get what is fundamentally wrong with using a `.Select`. It should work, unless what I do before is wrong, isn't it ? Is it possible that I encounter some memory issues due to the number of charts created ? Is there correct right may to "exit" a chart after adding it ? Thanks in advance – Tibo Jan 21 '15 at 00:26
  • @Tibo It is not fundamentally wrong to use `.Select`, it is just highly recommended to avoid using it whenever possible because it is often possible for something else to get manually selected while a macro is running, which would then make the `.Select` statement not work. I'm working on another option that will allow you to create a new chart and place it on the worksheet without having to copy and paste the original chart each time. How many data series do you have for each chart? Does it vary? – TheEngineer Jan 27 '15 at 20:22
  • @TheEngineer. The graph is quite simple : 3 series, 2 bar-chart, 1 line-chart on 2nd axis, partially fixed axis length, titles, formatting... I think I could **create** a graph rather than **copy** one, but I tried to avoid that because I tought I would be easier to : - modify the "model chart" when wanted (without avoid to modify the code) - place the graph where I want (that's why I was using the `Row.Select`) - limit the size of the sub (only 6 small lines when using `Copy/Paste`). It might be my mistake (all the more with 50+ graphs to "create"). Thanks for your help. Appreciated – Tibo Jan 28 '15 at 23:09

1 Answers1

0

UNTESTED

As @GSerg stated in the comments, you should always avoid using Select and using Rows without qualifying the sheet. Change your code to the following:

Sheets("Bilan RDF").Rows(ligne_UDI(1) & ":" & ligne_UDI(1) + 1).Paste
TheEngineer
  • 1,205
  • 1
  • 11
  • 19