Robert
08-09-2007, 11:13 PM
I make a chart show drawing because my client don't have AutoCAD, they only want to see on Excel. It's a tunnel wriggle typical section, and I put data on it evey 10m.
I have to put all the graphs on each worksheets, so I have to copy and change all these worksheet name and chartvalue, it took me huge, huge time, so could we find a macro way to do it with one button?
Thank you!
Sheets(Array("600", "G600")).Select
Sheets("600").Activate
Sheets(Array("600", "G600")).Copy Before:=Sheets(5)
Sheets("600 (2)").Select
Sheets("600 (2)").Name = "800"
Sheets("G600 (2)").Select
Sheets("G600 (2)").Name = "G800"
ActiveSheet.ChartObjects("圖表 2").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(2).XValues = "='800'!R5C1"
ActiveChart.SeriesCollection(2).Values = "='800'!R5C2"
ActiveChart.SeriesCollection(3).XValues = "='800'!R3C7:R51C7"
ActiveChart.SeriesCollection(3).Values = "='800'!R3C9:R51C9"
ActiveChart.SeriesCollection(4).XValues = "='800'!R20C1:R21C1"
ActiveChart.SeriesCollection(4).Values = "='800'!R20C2:R21C2"
ActiveChart.SeriesCollection(5).XValues = "='800'!R21C1:R22C1"
ActiveChart.SeriesCollection(5).Values = "='800'!R21C2:R22C2"
.......
I have to put all the graphs on each worksheets, so I have to copy and change all these worksheet name and chartvalue, it took me huge, huge time, so could we find a macro way to do it with one button?
Thank you!
Sheets(Array("600", "G600")).Select
Sheets("600").Activate
Sheets(Array("600", "G600")).Copy Before:=Sheets(5)
Sheets("600 (2)").Select
Sheets("600 (2)").Name = "800"
Sheets("G600 (2)").Select
Sheets("G600 (2)").Name = "G800"
ActiveSheet.ChartObjects("圖表 2").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(2).XValues = "='800'!R5C1"
ActiveChart.SeriesCollection(2).Values = "='800'!R5C2"
ActiveChart.SeriesCollection(3).XValues = "='800'!R3C7:R51C7"
ActiveChart.SeriesCollection(3).Values = "='800'!R3C9:R51C9"
ActiveChart.SeriesCollection(4).XValues = "='800'!R20C1:R21C1"
ActiveChart.SeriesCollection(4).Values = "='800'!R20C2:R21C2"
ActiveChart.SeriesCollection(5).XValues = "='800'!R21C1:R22C1"
ActiveChart.SeriesCollection(5).Values = "='800'!R21C2:R22C2"
.......