re table & chart

[Franck]

Hi everydody,
I've a probleme to do my chart (with vba) by using many tables having sames
columns.
What i mean?
i'm explain:
i've 4 tables on 4 diferents sheets: F1,F2,F3,F4
these tables have 2 same columns: the firs & the last, but they are
diferrents colums on each other

example:

sur F1: année|c1|c2|c3|ref
sur F2: année|c1|c2|c3|4c|c5|ref
sur F3: année|c1|c2|ref
sur F4: année|c1|c2|c3|c4|c5|c6|c7|ref

how to copy these table on a sheet:F5 and do the chart by using data on
sheet F5 by using vba
Please
Francky

 

[Jon Peltier]

I'd first have the macro find the "ref" column, and insert it after the
"annee" column. After that, by your simple diagram, the other columns
either line up, or the worksheet has no data in the column, and I assume
we'll just ignore that.

This moves the column in the active sheet. Adjust it to do each sheet as
you loop among them:

activesheet.cells.find("ref").entirecolumn.cut
activesheet.columns(2).insert shift:=xltoright

Then I would copy the used range of the worksheet, and paste it at the
bottom of the target worksheet. The "offset" leaves out the column labels.

Activesheet.usedrange.offset(1).copy _
worksheets("Target").cells(65536,1).end(xlup).offset(1,0)

I don't know what you're charting, but this will plot ref vs annee on a
chart sheet:

charts.Add after:=worksheets("Target")
activechart.ChartType=xlXYScatter
activechart.SetSourceData _
source:=worksheets("Target").usedrange.columns(2), plotby:=xlcolumns

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______