Applying macro to 'all opened files'

**Pedroluna** · 07-20-2009, 02:48 PM

Hi i have the following code:

HTML Code:

I need that operation to be run on over 20 files. in the exact same cells and colunmns. I can insert the code in macro i already have for each file. But I rather just open all the files and then have this procedure applied to 'all opened files'. is that possible? and it is can i have the code?
Thank you

**mrice** · 07-20-2009, 03:50 PM

Try

Sub Test()
For Each MyWorkbook In Workbooks
    MyWorkbook.Sheets(1).Range("B33").FormulaR1C1 = "=+R[-1]C*0.131"
    MyWorkbook.Sheets(1).Range("F33").FormulaR1C1 = "=+R[-1]C*0.141"
Next MyWorkbook
End Sub

I've assumed that you always want to work on the first sheet in the workbook.

**Pedroluna** · 07-21-2009, 12:05 PM

Martin, thank you.
you bring up a good point:'the sheet'. The sheet is the same in all files. it is name after the month of the year. So now i am working in the june files and i have done this operation in the June sheet of each file. Next month it will be the July sheet. How do i change the above code to pick the correct sheet?
Thanks again,

**mrice** · 07-21-2009, 02:12 PM

Sub Test()
MyMonth = Application.InputBox("Type in month name", , , , , , , 2)
For Each MyWorkbook In Workbooks
    MyWorkbook.Sheets(MyMonth).Range("B33").FormulaR1C1 = "=+R[-1]C*0.131"
    MyWorkbook.Sheets(MyMonth).Range("F33").FormulaR1C1 = "=+R[-1]C*0.141"
Next MyWorkbook
End Sub

Try the above