Excel Charts

[Phyre]

Does anyone know if it is possible to copy charts from Excel file No. 1 to Excel file No. 2 but have the pasted chart use the source data from Excel file No.2? A simple copy and paste does not work. The newly pasted file uses the source data from the original file.

Thanks for any info.,
Phyre

[Matthyahuw]

right-click on the chart itself, and choose source data, click in the data range box, then highlight the new data and hit ok...

[Phyre]

Oops, my bad. I should have been a little more clear. You'll see what I mean, Matty. LOL

I've got 200 files that I need to do this with. Each of these files have monthly readings from instruments that are updated every 15 mins. And, I need daily charts. So, for a full month and a few days over and under, I have roughly 3300 rows in each file. Multiply that by 200 and you have roughly 660,000 total rows.

That being said, I can do the right-click and edit the source data, but with the amount of data I have (about 30 charts per file), I would like to have this process a little more automated. It's a real bugger, man.

Thanks for the help.

Phyre

[Matthyahuw]

macro?

[Phyre]

Yeah. I started one of those, but being a macro newbie, I'm getting some errors. I've been able to assign variables to sheet names and charts using InputBoxes, but I cannot seem to get the ranges down. I either get a type mismatch or object or with block variable not set. I used the macro recorder and then went in to start assigning variables to stuff.

Here's the current state of the macro.

-----------------------------------------------------------------------------------

Sub Test()
'
' Test Macro
'
'
'

Dim ChartNo, Title, Default, Chart 'Prompt for inputting chart number to modify
ChartNo = "Chart No."
Title = "Chart No."
Default = "1"

Chart = InputBox(ChartNo, Title, Default)

Dim Sheets, Title2, Default2, Sheet 'Prompt for inputting sheets
Sheets = "Sheets to use"
Title2 = "Sheets"
Default2 = "1"

Sheet = InputBox(Sheets, Title2, Default2)

Dim Ranges, Title3, Default3 'Prompt for Cell Ranges
Dim Range1 As Range
Ranges = "Cell ranges to use"
Title3 = "Ranges"
Default3 = "A1:A1"

Dim Ranges2, Title4, Default4, Range2
Ranges2 = "Cell ranges to use"
Title4 = "Ranges"
Default4 = "A1:A1"

Range1 = InputBox(Ranges, Title3, Default3)
'Range2 = InputBox(Ranges2, Title4, Default4)

'ActiveSheet.ChartObjects(Chart).Activate
'ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets(Sheet).Range(Range1), _
PlotBy:=xlColumns
End Sub


-----------------------------------------------------------------------------------

It would be nice to enter ranges like b97:b192,d97:d192, but I just can't seem to get the macro to take it.

Thanks again,
Phyre

[ALobpreis]

Just a shot in the dark, but....
What about Edit / Special Paste?