Copy ranges
 

Hi there,

I want to write a Macro that will:

(i) Copy a range from Sheet1!B5:AK87 into Sheet2!B5:AK87;
(ii) For each cell in the new range in sheet 2, for each
cell that is neither blank nor text (i.e. for each cell
that is a number), inflate it's value by 2.5% for two
years (i.e. multiply each of these cells by 1.050625).

how do I do this?

Also, what would happen to the Macro if I inserted a row
into the original range in Sheet1? Would the Macro miss
out the last row when copying and inflating? If I name
the original range, does this solve the problem? (It is
likely that rows and/or columns will be inserted into the
original range at some point in the future, so tailoring
the Macro to suit this would really help me)

Many Thanks,

Gary.

Copy ranges
 

Dim cell As Range

Worksheets("Sheet1").Range("B5:AK87").Copy Destination:= _
Worksheets("Sheet2").Range("B5:AK87")

For Each cell In Worksheets("Sheet2").Range("B5:AK87")
If IsNumeric(cell.Value) And cell.Value < 0 Then
cell.Value = cell.Value * 1.025
End If
Next cell

As you supect, the last line will be missed as it will now be row 88. Naming
it and suing tghe name will hellp, as long as you use that name in the code.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gary Thomson" wrote in message
...
Hi there,

I want to write a Macro that will:

(i) Copy a range from Sheet1!B5:AK87 into Sheet2!B5:AK87;
(ii) For each cell in the new range in sheet 2, for each
cell that is neither blank nor text (i.e. for each cell
that is a number), inflate it's value by 2.5% for two
years (i.e. multiply each of these cells by 1.050625).

how do I do this?

Also, what would happen to the Macro if I inserted a row
into the original range in Sheet1? Would the Macro miss
out the last row when copying and inflating? If I name
the original range, does this solve the problem? (It is
likely that rows and/or columns will be inserted into the
original range at some point in the future, so tailoring
the Macro to suit this would really help me)

Many Thanks,

Gary.