Help with a repetitive task

[brubru24]

Hi All,

I am not sure if you can help but I have a spreadsheet with a lot of sheets (countries) and within each sheet there is a table showing a rate card Year by year.

Year-1	Year-2	Year-3	Year-4	Year-5	Year-6
Daily	Monthly	Daily	Monthly	Daily	Monthly	Daily	Monthly	Daily	Monthly	Daily	Monthly
$ 1,532	$ 23,289	$ 1,578	$ 23,987	$ 1,625	$ 24,707	$ 1,674	$ 25,448	$ 1,724	$ 26,212	$ 1,724	$ 26,212
$ 1,389	$ 21,107	$ 1,430	$ 21,740	$ 1,473	$ 22,393	$ 1,517	$ 23,064	$ 1,563	$ 23,756	$ 1,563	$ 23,756
$ 1,047	$ 15,912	$ 1,078	$ 16,389	$ 1,111	$ 16,881	$ 1,144	$ 17,387	$ 1,178	$ 17,909	$ 1,178	$ 17,909
$ 1,298	$ 19,727	$ 1,337	$ 20,319	$ 1,377	$ 20,928	$ 1,418	$ 21,556	$ 1,461	$ 22,203	$ 1,461	$ 22,203
$ 1,079	$ 16,400	$ 1,111	$ 16,892	$ 1,145	$ 17,399	$ 1,179	$ 17,921	$ 1,214	$ 18,459	$ 1,214	$ 18,459
$ 914	$ 13,890	$ 941	$ 14,307	$ 969	$ 14,736	$ 999	$ 15,178	$ 1,029	$ 15,633	$ 1,029	$ 15,633
$ 1,026	$ 15,592	$ 1,057	$ 16,060	$ 1,088	$ 16,541	$ 1,121	$ 17,038	$ 1,155	$ 17,549	$ 1,155	$ 17,549
$ 911	$ 13,843	$ 938	$ 14,259	$ 966	$ 14,686	$ 995	$ 15,127	$ 1,025	$ 15,581	$ 1,025	$ 15,581
$ 911	$ 13,843	$ 938	$ 14,259	$ 966	$ 14,686	$ 995	$ 15,127	$ 1,025	$ 15,581	$ 1,025	$ 15,581
$ 845	$ 12,846	$ 871	$ 13,232	$ 897	$ 13,629	$ 924	$ 14,038	$ 951	$ 14,459	$ 951	$ 14,459

<tbody>
</tbody>

Year-1		Year-2		Year-3		Year-4		Year-5		Year-6
Daily	Monthly	Daily	Monthly	Daily	Monthly	Daily	Monthly	Daily	Monthly	Daily	Monthly
$ 1,532	$ 23,289	$ 1,578	$ 23,987	$ 1,625	$ 24,707	$ 1,674	$ 25,448	$ 1,724	$ 26,212	$ 1,724	$ 26,212
$ 1,389	$ 21,107	$ 1,430	$ 21,740	$ 1,473	$ 22,393	$ 1,517	$ 23,064	$ 1,563	$ 23,756	$ 1,563	$ 23,756
$ 1,047	$ 15,912	$ 1,078	$ 16,389	$ 1,111	$ 16,881	$ 1,144	$ 17,387	$ 1,178	$ 17,909	$ 1,178	$ 17,909
$ 1,298	$ 19,727	$ 1,337	$ 20,319	$ 1,377	$ 20,928	$ 1,418	$ 21,556	$ 1,461	$ 22,203	$ 1,461	$ 22,203
$ 1,079	$ 16,400	$ 1,111	$ 16,892	$ 1,145	$ 17,399	$ 1,179	$ 17,921	$ 1,214	$ 18,459	$ 1,214	$ 18,459
$ 914	$ 13,890	$ 941	$ 14,307	$ 969	$ 14,736	$ 999	$ 15,178	$ 1,029	$ 15,633	$ 1,029	$ 15,633
$ 1,026	$ 15,592	$ 1,057	$ 16,060	$ 1,088	$ 16,541	$ 1,121	$ 17,038	$ 1,155	$ 17,549	$ 1,155	$ 17,549
$ 911	$ 13,843	$ 938	$ 14,259	$ 966	$ 14,686	$ 995	$ 15,127	$ 1,025	$ 15,581	$ 1,025	$ 15,581
$ 911	$ 13,843	$ 938	$ 14,259	$ 966	$ 14,686	$ 995	$ 15,127	$ 1,025	$ 15,581	$ 1,025	$ 15,581
$ 845	$ 12,846	$ 871	$ 13,232	$ 897	$ 13,629	$ 924	$ 14,038	$ 951	$ 14,459	$ 951	$ 14,459

<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;" span="12"> <tbody> </tbody>

What I would like to do is apply 3% increase year on year to the daily and monthly values from year 2 onwards compounded. So Y2 is X*1.03 and year 3 is year 2*1.03*1.03 and so forth. The repetitive task is be done in every sheet.

All these above table are placed in the same region in each sheet from year 2 (I9 = 1578 to R18 = 14459) as an example in the above.

Does anyone know some VBA code on how to do this please without doing it manually for every sheet (67) in the workbook.

Thank you
Bruno

 

[Aryatect]

Hi,

If in case you want to do this without VBA code, then all you'll need is to have Year 1 data in all the sheets. Then you can select all 67 sheets, while keeping all selected Place formula in C2 = A2*1.03 (assuming A2 has monthly values), copy it down and across, the formula will get copied across the sheets.

Point to note: All the sheets should have same number of rows and columns.

 

[brubru24]

Hello Aryatect

thanks for helping. The problem I have is that the values are different static numbers (rate cards). So I need to add 1.03 to each number from year 2 onwards.

hope it makes sense.

Kr

 

[Aryatect]

Ohkk, I checked the values for some years they were all 1.03 times the previous year so I thought copying is the issue.