how to run a macro on a group of files and merge results in a masterfile?

[flemak]

hey, I have a macro that creates a diagram and copies it to another file.. my code looks like this one:

'create diagram:

​

ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=Range( _
​

"'Tabelle1'!$F$3:$F$722")

ActiveChart.FullSeriesCollection(1).XValues = _
​

"='Tabelle1'!$B$3:$B$722"

'copy diagram:

​

ActiveChart.ChartArea.Select
Application.CutCopyMode = False
ActiveChart.ChartArea.Copy
Windows("Masterfile Diagramm.xlsx").Activate
ActiveSheet.ChartObjects("summary").Select
ActiveChart.Paste
​

I want to run this code on a group of Excel files (e.g. file1, file2, file3 and file4) without editing every single workbook of every file (without Autpen() or Workbook_Open) and also by referencing to the files with a loop.
Maybe similar like this(?):

dim i as integer
for i=1 to 20
<code class="vb plain">workbooks.open filename:= </code><code class="vb string">"file"</code> <code class="vb plain">& i</code>

<code class="vb plain">next i</code>




​

I cannot figure out a way to do it without writing a sub Workbook_open() macro in every file which is too much work if I have 50 or more files to run the macro on.

Thanks for your help and have a good weekend!

 

[CsJHUN]

Hi, and welcome aboard!

Please take a look on this loopthrough.

Good luck

 

[flemak]

Thank you, that was exactly what I was looking for!