MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Looping Structure - "For-Each" statements

Posted by Sam Castiglione on December 12, 2000 12:02 PM

I work in a Budgets/Costing section and have the following question: I have 70 spreadsheets which represent cost centres (example:0712.xls,0713.xls,etc.). I have created another spreadsheet (calunion.xls), and I want to paste a certain range of this spreadsheet ( from calunion.xls) to all 70 spreadsheets.I have created a macro to paste the specific range from calunion.xls to let say 0712.xls. Now, I have to replace 0712.xls with let say 0713.xls in the macro and then do that for all all the other 68 spreadsheets. Is
there a way to list all the cost centres in one spreadsheet or part of the existing macro, so that I don't have to REPLACE the cost centre each time and run the MACRO (70 times). I believe I have to create some "looping structure" but I am not sure. Your help with the syntax would be appreciated.

Posted by Greg on December 12, 2000 5:54 PM

If you have all the sheets in the same folder you can use the Dir function in your loop.