Procedure too large - how can I shorten my code?

[nic_hartley]

I have written a macro to work from an command button to update sales reports. This command button will update the master sheet, then create, rename and save etc individual files to be emailed out to the sales guys.

The code is now too long as I am still quite new to using visual basic I do not know how to write a more sophisicated code. Please see part of my code below which im sure there is a way to shorten:

Sheets("Budget").Activate
Range("BW90").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-72],RC[-66])"
Range("BW91").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-72],RC[-66])"
Range("BW92").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-72],RC[-66])"
Range("BW93").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-72],RC[-66])"
Range("BW94").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-72],RC[-66])"
Range("BW95").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-72],RC[-66])"
Range("BW96").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-72],RC[-66])"
Range("BW97").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-72],RC[-66])"
Range("BW98").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-72],RC[-66])"
Range("BW99").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-72],RC[-66])"
Range("BW100").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-72],RC[-66])"
Range("BW101").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-72],RC[-66])"
Range("BW103").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-72],RC[-66])"


This is repeated for four areas of the spreadsheet and repeated for each of the 7 sales guys - there is surely a way to loop this?

Any help would be really appreciated!

Thanks!

 

[Jonmo1]

Welcome to the board...

Try

Sheets("Budget").Range("BW90:BW103").FormulaR1C1 = "=SUM(RC[-72],RC[-66])"


Hope that helps..

 

[VoG]

You can replace that with

Sheets("Budget").Activate
Range("BW90:BW103").FormulaR1C1 = "=SUM(RC[-72],RC[-66])"

 

[nic_hartley]

Brilliant - thank you both - works a treat!