Help with a repetitive task

B

brubru24

New Member
Joined
May 29, 2011
Messages
12
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.

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

[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 672"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;" span="12"> <tbody>[TR]
[TD="width: 75, bgcolor: #D8E4BC"] Year-1 [/TD]
[TD="width: 75, bgcolor: #D8E4BC"] [/TD]
[TD="width: 75, bgcolor: #D8E4BC"] Year-2 [/TD]
[TD="width: 75, bgcolor: #D8E4BC"] [/TD]
[TD="width: 75, bgcolor: #D8E4BC"] Year-3 [/TD]
[TD="width: 75, bgcolor: #D8E4BC"] [/TD]
[TD="width: 75, bgcolor: #D8E4BC"] Year-4 [/TD]
[TD="width: 75, bgcolor: #D8E4BC"] [/TD]
[TD="width: 75, bgcolor: #D8E4BC"] Year-5 [/TD]
[TD="width: 75, bgcolor: #D8E4BC"] [/TD]
[TD="width: 75, bgcolor: #D8E4BC"] Year-6 [/TD]
[TD="width: 75, bgcolor: #D8E4BC"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #D8E4BC"] Daily [/TD]
[TD="bgcolor: #D8E4BC"] Monthly [/TD]
[TD="bgcolor: #D8E4BC"] Daily [/TD]
[TD="bgcolor: #D8E4BC"] Monthly [/TD]
[TD="bgcolor: #D8E4BC"] Daily [/TD]
[TD="bgcolor: #D8E4BC"] Monthly [/TD]
[TD="bgcolor: #D8E4BC"] Daily [/TD]
[TD="bgcolor: #D8E4BC"] Monthly [/TD]
[TD="bgcolor: #D8E4BC"] Daily [/TD]
[TD="bgcolor: #D8E4BC"] Monthly [/TD]
[TD="bgcolor: #D8E4BC"] Daily [/TD]
[TD="bgcolor: #D8E4BC"] Monthly [/TD]
[/TR]
[TR]
[TD="bgcolor: #D8E4BC"] $ 1,532 [/TD]
[TD="bgcolor: #D8E4BC"] $ 23,289 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,578 [/TD]
[TD="bgcolor: #D8E4BC"] $ 23,987 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,625 [/TD]
[TD="bgcolor: #D8E4BC"] $ 24,707 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,674 [/TD]
[TD="bgcolor: #D8E4BC"] $ 25,448 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,724 [/TD]
[TD="bgcolor: #D8E4BC"] $ 26,212 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,724 [/TD]
[TD="bgcolor: #D8E4BC"] $ 26,212 [/TD]
[/TR]
[TR]
[TD="bgcolor: #D8E4BC"] $ 1,389 [/TD]
[TD="bgcolor: #D8E4BC"] $ 21,107 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,430 [/TD]
[TD="bgcolor: #D8E4BC"] $ 21,740 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,473 [/TD]
[TD="bgcolor: #D8E4BC"] $ 22,393 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,517 [/TD]
[TD="bgcolor: #D8E4BC"] $ 23,064 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,563 [/TD]
[TD="bgcolor: #D8E4BC"] $ 23,756 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,563 [/TD]
[TD="bgcolor: #D8E4BC"] $ 23,756 [/TD]
[/TR]
[TR]
[TD="bgcolor: #D8E4BC"] $ 1,047 [/TD]
[TD="bgcolor: #D8E4BC"] $ 15,912 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,078 [/TD]
[TD="bgcolor: #D8E4BC"] $ 16,389 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,111 [/TD]
[TD="bgcolor: #D8E4BC"] $ 16,881 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,144 [/TD]
[TD="bgcolor: #D8E4BC"] $ 17,387 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,178 [/TD]
[TD="bgcolor: #D8E4BC"] $ 17,909 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,178 [/TD]
[TD="bgcolor: #D8E4BC"] $ 17,909 [/TD]
[/TR]
[TR]
[TD="bgcolor: #D8E4BC"] $ 1,298 [/TD]
[TD="bgcolor: #D8E4BC"] $ 19,727 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,337 [/TD]
[TD="bgcolor: #D8E4BC"] $ 20,319 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,377 [/TD]
[TD="bgcolor: #D8E4BC"] $ 20,928 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,418 [/TD]
[TD="bgcolor: #D8E4BC"] $ 21,556 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,461 [/TD]
[TD="bgcolor: #D8E4BC"] $ 22,203 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,461 [/TD]
[TD="bgcolor: #D8E4BC"] $ 22,203 [/TD]
[/TR]
[TR]
[TD="bgcolor: #D8E4BC"] $ 1,079 [/TD]
[TD="bgcolor: #D8E4BC"] $ 16,400 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,111 [/TD]
[TD="bgcolor: #D8E4BC"] $ 16,892 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,145 [/TD]
[TD="bgcolor: #D8E4BC"] $ 17,399 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,179 [/TD]
[TD="bgcolor: #D8E4BC"] $ 17,921 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,214 [/TD]
[TD="bgcolor: #D8E4BC"] $ 18,459 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,214 [/TD]
[TD="bgcolor: #D8E4BC"] $ 18,459 [/TD]
[/TR]
[TR]
[TD="bgcolor: #D8E4BC"] $ 914 [/TD]
[TD="bgcolor: #D8E4BC"] $ 13,890 [/TD]
[TD="bgcolor: #D8E4BC"] $ 941 [/TD]
[TD="bgcolor: #D8E4BC"] $ 14,307 [/TD]
[TD="bgcolor: #D8E4BC"] $ 969 [/TD]
[TD="bgcolor: #D8E4BC"] $ 14,736 [/TD]
[TD="bgcolor: #D8E4BC"] $ 999 [/TD]
[TD="bgcolor: #D8E4BC"] $ 15,178 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,029 [/TD]
[TD="bgcolor: #D8E4BC"] $ 15,633 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,029 [/TD]
[TD="bgcolor: #D8E4BC"] $ 15,633 [/TD]
[/TR]
[TR]
[TD="bgcolor: #D8E4BC"] $ 1,026 [/TD]
[TD="bgcolor: #D8E4BC"] $ 15,592 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,057 [/TD]
[TD="bgcolor: #D8E4BC"] $ 16,060 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,088 [/TD]
[TD="bgcolor: #D8E4BC"] $ 16,541 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,121 [/TD]
[TD="bgcolor: #D8E4BC"] $ 17,038 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,155 [/TD]
[TD="bgcolor: #D8E4BC"] $ 17,549 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,155 [/TD]
[TD="bgcolor: #D8E4BC"] $ 17,549 [/TD]
[/TR]
[TR]
[TD="bgcolor: #D8E4BC"] $ 911 [/TD]
[TD="bgcolor: #D8E4BC"] $ 13,843 [/TD]
[TD="bgcolor: #D8E4BC"] $ 938 [/TD]
[TD="bgcolor: #D8E4BC"] $ 14,259 [/TD]
[TD="bgcolor: #D8E4BC"] $ 966 [/TD]
[TD="bgcolor: #D8E4BC"] $ 14,686 [/TD]
[TD="bgcolor: #D8E4BC"] $ 995 [/TD]
[TD="bgcolor: #D8E4BC"] $ 15,127 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,025 [/TD]
[TD="bgcolor: #D8E4BC"] $ 15,581 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,025 [/TD]
[TD="bgcolor: #D8E4BC"] $ 15,581 [/TD]
[/TR]
[TR]
[TD="bgcolor: #D8E4BC"] $ 911 [/TD]
[TD="bgcolor: #D8E4BC"] $ 13,843 [/TD]
[TD="bgcolor: #D8E4BC"] $ 938 [/TD]
[TD="bgcolor: #D8E4BC"] $ 14,259 [/TD]
[TD="bgcolor: #D8E4BC"] $ 966 [/TD]
[TD="bgcolor: #D8E4BC"] $ 14,686 [/TD]
[TD="bgcolor: #D8E4BC"] $ 995 [/TD]
[TD="bgcolor: #D8E4BC"] $ 15,127 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,025 [/TD]
[TD="bgcolor: #D8E4BC"] $ 15,581 [/TD]
[TD="bgcolor: #D8E4BC"] $ 1,025 [/TD]
[TD="bgcolor: #D8E4BC"] $ 15,581 [/TD]
[/TR]
[TR]
[TD="bgcolor: #D8E4BC"] $ 845 [/TD]
[TD="bgcolor: #D8E4BC"] $ 12,846 [/TD]
[TD="bgcolor: #D8E4BC"] $ 871 [/TD]
[TD="bgcolor: #D8E4BC"] $ 13,232 [/TD]
[TD="bgcolor: #D8E4BC"] $ 897 [/TD]
[TD="bgcolor: #D8E4BC"] $ 13,629 [/TD]
[TD="bgcolor: #D8E4BC"] $ 924 [/TD]
[TD="bgcolor: #D8E4BC"] $ 14,038 [/TD]
[TD="bgcolor: #D8E4BC"] $ 951 [/TD]
[TD="bgcolor: #D8E4BC"] $ 14,459 [/TD]
[TD="bgcolor: #D8E4BC"] $ 951 [/TD]
[TD="bgcolor: #D8E4BC"] $ 14,459 [/TD]
[/TR]
</tbody>[/TABLE]




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
 
Last edited:

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
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.
 
Upvote 0
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
 
Upvote 0
Ohkk, I checked the values for some years they were all 1.03 times the previous year so I thought copying is the issue.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top