creating graphs

H

hj

i have a file with numerous tabs for individual projects. all the tabs are consistently formatted so that the data is in the exact same spot on each spreadsheet. i have created five standard charts that I would like created for each individual tab. is there an easy way to create a macro that will create the charts for each individual tab, save to another file and then move onto the next tab to repeat the process so that i end up with five charts per project?
 
N

Nicky

Yes, but without seeing your sheet its hard to tell exactly what wil
work
assuming that you want 5 charts from worksheets Sheet1 to Sheet10, an
the charts are stored on chart sheets named chart1 to chart5, and ar
currently showing the data from Sheet1, then something like this migh
work:


Sub export_charts()
sfile = ActiveWorkbook.Name
Workbooks.Add
nfile = ActiveWindow.Caption
Windows(sfile).Activate

sourcesheets = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5"
"Sheet6", "Sheet7", "Sheet8", "Sheet9", "Sheet10")
chartsheets = Array("Chart1", "Chart2", "Chart3", "Chart4", "Chart5")
For n = LBound(sourcesheets) To UBound(sourcesheets)
If n = UBound(sourcesheets) Then nn = 1 Else nn = n + 1
For z = LBound(chartsheets) To UBound(chartsheets)
Sheets(chartsheets(z)).Select
For Each ss In ActiveChart.SeriesCollection
txt = Replace(ss.Formula, sourcesheets(n), sourcesheets(nn))
ss.Formula = txt
Next ' next series
Sheets(chartsheets(z)).Cop
after:=Workbooks(nfile).Sheets(Workbooks(nfile).Sheets.Count)
Windows(sfile).Activate
Next ' next chartsheet
Next 'next sourcesheet

End Su
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top