Hello,
I've just joined this interesting forum because I am looking for a solution to a problem. I checked a lot of threads and I couldn't find the right answer, so here it goes:
- I have Workbook 1 called "April", where there are 7 Sheets. Each of these sheets has a cell where is a total amount (euro).
- There is Workbook A with one cell where I want to use SUM function and add all of the total amounts from the Sheets of April
- I created a Macro where I recorded a 3-D range formula which works ok.
But the problem appears when I run this Macro to calculate the total amount into Workbook A but from a different Workbook, say from January or February and so on, because these reports are changing all the time. I get the error which shows me that the Macro referred to April and it didn't calculate the total amount from the new Workbook, say January.
Here is the formula that works for April:
=SUM('[April.xls]1:7'!$I$2:$I$3)
And the Macro code for this formula:
Windows("April.xls").Activate
Range("I2:I3").Select
Sheets(Array("1", "2", "3", "4", "5", "6", "7")).Select
Sheets("1").Activate
Windows("New Foaie de lucru Microsoft Excel.xls").Activate
Range("A1").Select
Selection.FormulaR1C1 = "=SUM('[April.xls]1:7'!R2C9:R3C9)"
End Sub
Could you please give me a hint how to make the formula so that it will update it every time I have a new report? Because now when I run it on a new report, it points always to April. And also, the number of sheets is different each time, how can I update the formula so that it will always calculate all of the sheets?
Thank you so much!
I've just joined this interesting forum because I am looking for a solution to a problem. I checked a lot of threads and I couldn't find the right answer, so here it goes:
- I have Workbook 1 called "April", where there are 7 Sheets. Each of these sheets has a cell where is a total amount (euro).
- There is Workbook A with one cell where I want to use SUM function and add all of the total amounts from the Sheets of April
- I created a Macro where I recorded a 3-D range formula which works ok.
But the problem appears when I run this Macro to calculate the total amount into Workbook A but from a different Workbook, say from January or February and so on, because these reports are changing all the time. I get the error which shows me that the Macro referred to April and it didn't calculate the total amount from the new Workbook, say January.
Here is the formula that works for April:
=SUM('[April.xls]1:7'!$I$2:$I$3)
And the Macro code for this formula:
Windows("April.xls").Activate
Range("I2:I3").Select
Sheets(Array("1", "2", "3", "4", "5", "6", "7")).Select
Sheets("1").Activate
Windows("New Foaie de lucru Microsoft Excel.xls").Activate
Range("A1").Select
Selection.FormulaR1C1 = "=SUM('[April.xls]1:7'!R2C9:R3C9)"
End Sub
Could you please give me a hint how to make the formula so that it will update it every time I have a new report? Because now when I run it on a new report, it points always to April. And also, the number of sheets is different each time, how can I update the formula so that it will always calculate all of the sheets?
Thank you so much!
