Totals An Easier Way??

S

Sue

Hi All

I have just used the macro recorder and was wondering if there is an easier
and less complicated way than the code below

Sub Macro4()
Application.ScreenUpdating = False
Sheets("Com;In;Av").Select
Range("A6:B6").Select
Selection.Copy
Range("A7").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E7").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/d)"
Range("F7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A9:B9").Select
Selection.Copy
Range("A10").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C10").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D10").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E10").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F10").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("F10").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A11:B11").Select
Selection.Copy
Range("A13").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C13").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D13").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E13").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F13").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("F13").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A15:B15").Select
Selection.Copy
Range("A16").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C16").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D16").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E16").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F16").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A18:B18").Select
Selection.Copy
Range("A19").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C19").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D19").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("D19").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E19").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F19").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E7").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("A21:B21").Select
Selection.Copy
Range("A22").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C22").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D22").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E22").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F22").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A24:B24").Select
Selection.Copy
Range("A25").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C25").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D25").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E25").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F25").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A27:B27").Select
Selection.Copy
Range("A28").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C28").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D28").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E28").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F28").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E29").Select
ActiveWindow.SmallScroll Down:=19
Range("A30:B30").Select
Selection.Copy
Range("A31").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C31").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D31").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E31").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F31").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A33:B33").Select
Selection.Copy
Range("A34").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C34").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D34").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E34").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F34").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A36:B36").Select
Selection.Copy
Range("A37").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E37").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"


Range("A39:B39").Select
Selection.Copy
Range("A40").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D40").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("D40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C/R[-1]C)"
Range("F40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A42:B42").Select
Selection.Copy
Range("A43").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[1]C[-2]/R[1]C[-1])"
Range("E43").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A45:B45").Select
Selection.Copy
Range("A46").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C46").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("D46").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("E46").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F46").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A48:B48").Select
Selection.Copy
Range("A49").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C49").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D49").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E49").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F49").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A51:B51").Select
Selection.Copy
Range("A52").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C52").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("D52").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("E52").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F52").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A54:B54").Select
Selection.Copy
Range("A55").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C55").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("D55").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("E55").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F55").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A57:B57").Select
Selection.Copy
Range("A58").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C58").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D58").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E58").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F58").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("E59").Select

Range("A7:F7").Select
Selection.Copy

Range("A70").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A10:F10").Select
Application.CutCopyMode = False
Selection.Copy

Range("A71").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A13:F13").Select
Application.CutCopyMode = False
Selection.Copy

Range("A72").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


Range("A16:F16").Select
Application.CutCopyMode = False
Selection.Copy

Range("A73").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A19:F19").Select
Application.CutCopyMode = False
Selection.Copy


Range("A74").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A22:F22").Select
Application.CutCopyMode = False
Selection.Copy

Range("A75").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


Range("A25:F25").Select
Application.CutCopyMode = False
Selection.Copy


Range("A76").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A28:F28").Select
Application.CutCopyMode = False
Selection.Copy

Range("A77").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A31:F31").Select
Application.CutCopyMode = False
Selection.Copy

Range("A78").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A34:F34").Select
Application.CutCopyMode = False
Selection.Copy

Range("A79").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A37:F37").Select
Application.CutCopyMode = False
Selection.Copy

Range("A80").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A40:F40").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll Down:=25
Range("A81").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A43:F43").Select
Application.CutCopyMode = False
Selection.Copy

Range("A82").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


Range("A46:F46").Select
Application.CutCopyMode = False
Selection.Copy

Range("A83").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A49:F49").Select
Application.CutCopyMode = False
Selection.Copy

Range("A84").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A52:F52").Select
Application.CutCopyMode = False
Selection.Copy

Range("A85").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A55:F55").Select
Application.CutCopyMode = False
Selection.Copy

Range("A86").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A58:F58").Select
Application.CutCopyMode = False
Selection.Copy

Range("A87").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False

Range("A70:F87").Select
ActiveWorkbook.Worksheets("Com;In;Av").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Com;In;Av").Sort.SortFields.Add
Key:=Range("F70:F87"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Com;In;Av").Sort.SortFields.Add
Key:=Range("E70:E87"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Com;In;Av").Sort
.SetRange Range("A70:F87")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Application.ScreenUpdating = True
End Sub
 
B

Barb Reinhardt

Here is a partial example. I think you can figure it out from here.

Dim myRange As Range
Set myRange = Union(Range("C7"), Range("C10"), Range("C13"), Range("C16"))
myRange.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Set myRange = Nothing
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



Sue said:
Hi All

I have just used the macro recorder and was wondering if there is an easier
and less complicated way than the code below

Sub Macro4()
Application.ScreenUpdating = False
Sheets("Com;In;Av").Select
Range("A6:B6").Select
Selection.Copy
Range("A7").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E7").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/d)"
Range("F7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A9:B9").Select
Selection.Copy
Range("A10").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C10").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D10").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E10").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F10").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("F10").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A11:B11").Select
Selection.Copy
Range("A13").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C13").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D13").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E13").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F13").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("F13").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A15:B15").Select
Selection.Copy
Range("A16").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C16").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D16").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E16").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F16").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A18:B18").Select
Selection.Copy
Range("A19").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C19").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D19").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("D19").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E19").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F19").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E7").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("A21:B21").Select
Selection.Copy
Range("A22").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C22").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D22").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E22").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F22").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A24:B24").Select
Selection.Copy
Range("A25").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C25").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D25").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E25").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F25").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A27:B27").Select
Selection.Copy
Range("A28").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C28").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D28").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E28").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F28").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E29").Select
ActiveWindow.SmallScroll Down:=19
Range("A30:B30").Select
Selection.Copy
Range("A31").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C31").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D31").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E31").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F31").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A33:B33").Select
Selection.Copy
Range("A34").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C34").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D34").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E34").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F34").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A36:B36").Select
Selection.Copy
Range("A37").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E37").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"


Range("A39:B39").Select
Selection.Copy
Range("A40").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D40").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("D40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C/R[-1]C)"
Range("F40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A42:B42").Select
Selection.Copy
Range("A43").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[1]C[-2]/R[1]C[-1])"
Range("E43").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A45:B45").Select
Selection.Copy
Range("A46").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C46").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("D46").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("E46").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F46").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A48:B48").Select
Selection.Copy
Range("A49").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C49").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D49").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E49").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F49").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A51:B51").Select
Selection.Copy
Range("A52").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C52").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("D52").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("E52").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F52").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A54:B54").Select
Selection.Copy
Range("A55").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C55").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("D55").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("E55").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F55").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A57:B57").Select
Selection.Copy
Range("A58").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C58").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D58").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E58").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F58").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("E59").Select

Range("A7:F7").Select
Selection.Copy

Range("A70").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A10:F10").Select
Application.CutCopyMode = False
Selection.Copy

Range("A71").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A13:F13").Select
Application.CutCopyMode = False
Selection.Copy

Range("A72").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


Range("A16:F16").Select
Application.CutCopyMode = False
Selection.Copy

Range("A73").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A19:F19").Select
Application.CutCopyMode = False
Selection.Copy
Click to expand...
 
B

Bernard Liengme

Hi Sue,
There is no need to select a cell before you do something to it -- took me a
while to grasp this concept!
The macro below does what the first 16 lines of your code does. As you
progress, the code saving will increase.

Sub Macro4()
Application.ScreenUpdating = False
Set mysheets = Sheets(Array("com", "in", "av"))
For Each ws In mysheets
ws.Activate
Range("A6:B6").Copy Range("A7") 'note that Copy can
specify a target
For j = 3 To 6
Cells(7, j).FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Next j
Next ws
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Sue said:
Hi All

I have just used the macro recorder and was wondering if there is an
easier
and less complicated way than the code below

Sub Macro4()
Application.ScreenUpdating = False
Sheets("Com;In;Av").Select
Range("A6:B6").Select
Selection.Copy
Range("A7").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E7").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/d)"
Range("F7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A9:B9").Select
Selection.Copy
Range("A10").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C10").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D10").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E10").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F10").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("F10").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A11:B11").Select
Selection.Copy
Range("A13").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C13").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D13").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E13").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F13").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("F13").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A15:B15").Select
Selection.Copy
Range("A16").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C16").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D16").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E16").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F16").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A18:B18").Select
Selection.Copy
Range("A19").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C19").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D19").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("D19").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E19").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F19").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E7").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("A21:B21").Select
Selection.Copy
Range("A22").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C22").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D22").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E22").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F22").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A24:B24").Select
Selection.Copy
Range("A25").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C25").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D25").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E25").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F25").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A27:B27").Select
Selection.Copy
Range("A28").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C28").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D28").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E28").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F28").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E29").Select
ActiveWindow.SmallScroll Down:=19
Range("A30:B30").Select
Selection.Copy
Range("A31").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C31").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D31").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E31").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F31").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A33:B33").Select
Selection.Copy
Range("A34").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C34").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D34").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E34").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F34").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A36:B36").Select
Selection.Copy
Range("A37").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E37").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"


Range("A39:B39").Select
Selection.Copy
Range("A40").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D40").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("D40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C/R[-1]C)"
Range("F40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A42:B42").Select
Selection.Copy
Range("A43").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[1]C[-2]/R[1]C[-1])"
Range("E43").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A45:B45").Select
Selection.Copy
Range("A46").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C46").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("D46").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("E46").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F46").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A48:B48").Select
Selection.Copy
Range("A49").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C49").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D49").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E49").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F49").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A51:B51").Select
Selection.Copy
Range("A52").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C52").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("D52").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("E52").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F52").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A54:B54").Select
Selection.Copy
Range("A55").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C55").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("D55").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("E55").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F55").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A57:B57").Select
Selection.Copy
Range("A58").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C58").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D58").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E58").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F58").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("E59").Select

Range("A7:F7").Select
Selection.Copy

Range("A70").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A10:F10").Select
Application.CutCopyMode = False
Selection.Copy

Range("A71").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A13:F13").Select
Application.CutCopyMode = False
Selection.Copy

Range("A72").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


Range("A16:F16").Select
Application.CutCopyMode = False
Selection.Copy

Range("A73").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A19:F19").Select
Application.CutCopyMode = False
Selection.Copy


Range("A74").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A22:F22").Select
Application.CutCopyMode = False
Selection.Copy

Range("A75").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


Range("A25:F25").Select
Application.CutCopyMode = False
Selection.Copy


Range("A76").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A28:F28").Select
Application.CutCopyMode = False
Selection.Copy

Range("A77").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A31:F31").Select
Application.CutCopyMode = False
Selection.Copy

Range("A78").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A34:F34").Select
Application.CutCopyMode = False
Selection.Copy

Range("A79").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A37:F37").Select
Application.CutCopyMode = False
Selection.Copy

Range("A80").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A40:F40").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll Down:=25
Range("A81").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A43:F43").Select
Application.CutCopyMode = False
Selection.Copy

Range("A82").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


Range("A46:F46").Select
Application.CutCopyMode = False
Selection.Copy

Range("A83").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A49:F49").Select
Application.CutCopyMode = False
Selection.Copy

Range("A84").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A52:F52").Select
Application.CutCopyMode = False
Selection.Copy

Range("A85").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A55:F55").Select
Application.CutCopyMode = False
Selection.Copy

Range("A86").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A58:F58").Select
Application.CutCopyMode = False
Selection.Copy

Range("A87").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False

Range("A70:F87").Select
ActiveWorkbook.Worksheets("Com;In;Av").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Com;In;Av").Sort.SortFields.Add
Key:=Range("F70:F87"), _
SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Com;In;Av").Sort.SortFields.Add
Key:=Range("E70:E87"), _
SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Com;In;Av").Sort
.SetRange Range("A70:F87")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Application.ScreenUpdating = True
End Sub
Click to expand...
 
S

Sue

Hi Bernard

Thanks for the quick reply and it worked Ok when I changed the Sheet Name
see below

Sub Macro4 ()
Application.ScreenUpdating = False
Set mysheets = Sheets(Array("Com;In;Av"))
For Each ws In mysheets
ws.Activate
Range("A6:B6").Copy Range("A7") 'note that Copy can specify a target
For j = 3 To 6
Cells(7, j).FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Next j
Next ws
End Sub

However in column E the calc is a division as below
Range("E5").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("E6").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("E7").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"

And I don't know how to fit this into your code?

--
Many Thanks

Sue


Bernard Liengme said:
Hi Sue,
There is no need to select a cell before you do something to it -- took me a
while to grasp this concept!
The macro below does what the first 16 lines of your code does. As you
progress, the code saving will increase.

Sub Macro4()
Application.ScreenUpdating = False
Set mysheets = Sheets(Array("com", "in", "av"))
For Each ws In mysheets
ws.Activate
Range("A6:B6").Copy Range("A7") 'note that Copy can
specify a target
For j = 3 To 6
Cells(7, j).FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Next j
Next ws
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Sue said:
Hi All

I have just used the macro recorder and was wondering if there is an
easier
and less complicated way than the code below

Sub Macro4()
Application.ScreenUpdating = False
Sheets("Com;In;Av").Select
Range("A6:B6").Select
Selection.Copy
Range("A7").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E7").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/d)"
Range("F7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A9:B9").Select
Selection.Copy
Range("A10").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C10").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D10").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E10").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F10").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("F10").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A11:B11").Select
Selection.Copy
Range("A13").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C13").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D13").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E13").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F13").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("F13").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A15:B15").Select
Selection.Copy
Range("A16").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C16").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D16").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E16").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F16").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A18:B18").Select
Selection.Copy
Range("A19").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C19").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D19").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("D19").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E19").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F19").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E7").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("A21:B21").Select
Selection.Copy
Range("A22").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C22").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D22").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E22").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F22").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A24:B24").Select
Selection.Copy
Range("A25").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C25").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D25").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E25").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F25").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A27:B27").Select
Selection.Copy
Range("A28").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C28").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D28").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E28").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F28").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E29").Select
ActiveWindow.SmallScroll Down:=19
Range("A30:B30").Select
Selection.Copy
Range("A31").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C31").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D31").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E31").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F31").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A33:B33").Select
Selection.Copy
Range("A34").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C34").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D34").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E34").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F34").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A36:B36").Select
Selection.Copy
Range("A37").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E37").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"


Range("A39:B39").Select
Selection.Copy
Range("A40").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D40").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("D40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C/R[-1]C)"
Range("F40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A42:B42").Select
Selection.Copy
Range("A43").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[1]C[-2]/R[1]C[-1])"
Range("E43").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A45:B45").Select
Selection.Copy
Range("A46").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C46").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("D46").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("E46").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F46").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A48:B48").Select
Selection.Copy
Range("A49").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C49").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D49").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E49").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F49").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A51:B51").Select
Selection.Copy
Range("A52").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C52").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("D52").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("E52").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F52").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A54:B54").Select
Selection.Copy
Range("A55").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C55").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("D55").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("E55").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F55").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A57:B57").Select
Selection.Copy
Range("A58").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C58").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D58").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E58").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F58").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("E59").Select

Range("A7:F7").Select
Selection.Copy

Range("A70").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A10:F10").Select
Application.CutCopyMode = False
Selection.Copy
Click to expand...
Click to expand...
 
S

Sue

Hi Bernard

Got it wrong the Division is just in ColumnE - Row 7 not 5 & 6 as indicated
in previous post

--
Many Thanks

Sue


Sue said:
Hi Bernard

Thanks for the quick reply and it worked Ok when I changed the Sheet Name
see below

Sub Macro4 ()
Application.ScreenUpdating = False
Set mysheets = Sheets(Array("Com;In;Av"))
For Each ws In mysheets
ws.Activate
Range("A6:B6").Copy Range("A7") 'note that Copy can specify a target
For j = 3 To 6
Cells(7, j).FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Next j
Next ws
End Sub

However in column E the calc is a division as below
Range("E5").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("E6").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("E7").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"

And I don't know how to fit this into your code?

--
Many Thanks

Sue


Bernard Liengme said:
Hi Sue,
There is no need to select a cell before you do something to it -- took me a
while to grasp this concept!
The macro below does what the first 16 lines of your code does. As you
progress, the code saving will increase.

Sub Macro4()
Application.ScreenUpdating = False
Set mysheets = Sheets(Array("com", "in", "av"))
For Each ws In mysheets
ws.Activate
Range("A6:B6").Copy Range("A7") 'note that Copy can
specify a target
For j = 3 To 6
Cells(7, j).FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Next j
Next ws
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Sue said:
Hi All

I have just used the macro recorder and was wondering if there is an
easier
and less complicated way than the code below

Sub Macro4()
Application.ScreenUpdating = False
Sheets("Com;In;Av").Select
Range("A6:B6").Select
Selection.Copy
Range("A7").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E7").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/d)"
Range("F7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A9:B9").Select
Selection.Copy
Range("A10").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C10").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D10").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E10").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F10").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("F10").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A11:B11").Select
Selection.Copy
Range("A13").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C13").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D13").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E13").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F13").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("F13").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A15:B15").Select
Selection.Copy
Range("A16").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C16").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D16").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E16").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F16").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A18:B18").Select
Selection.Copy
Range("A19").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C19").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D19").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("D19").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E19").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F19").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E7").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("A21:B21").Select
Selection.Copy
Range("A22").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C22").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D22").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E22").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F22").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A24:B24").Select
Selection.Copy
Range("A25").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C25").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D25").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E25").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F25").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A27:B27").Select
Selection.Copy
Range("A28").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C28").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D28").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E28").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F28").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E29").Select
ActiveWindow.SmallScroll Down:=19
Range("A30:B30").Select
Selection.Copy
Range("A31").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C31").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D31").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E31").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F31").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A33:B33").Select
Selection.Copy
Range("A34").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C34").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D34").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E34").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F34").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A36:B36").Select
Selection.Copy
Range("A37").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E37").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"


Range("A39:B39").Select
Selection.Copy
Range("A40").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D40").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("D40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C/R[-1]C)"
Range("F40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A42:B42").Select
Selection.Copy
Range("A43").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[1]C[-2]/R[1]C[-1])"
Range("E43").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A45:B45").Select
Selection.Copy
Range("A46").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C46").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("D46").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("E46").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F46").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A48:B48").Select
Selection.Copy
Range("A49").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C49").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D49").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E49").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F49").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A51:B51").Select
Selection.Copy
Range("A52").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C52").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("D52").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("E52").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F52").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A54:B54").Select
Selection.Copy
Range("A55").Select
ActiveSheet.Paste
Click to expand...
Click to expand...
Click to expand...
 
B

Bernard Liengme

Replace
Range("E5").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("E6").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("E7").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"


BY

For j = 5 to 7
Cells(j, 5).FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
next j

NOTE this also works but involves more typing (therefore is more
error-prone!)
Range("E5").FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("E6").FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("E7").FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Sue said:
Hi Bernard

Thanks for the quick reply and it worked Ok when I changed the Sheet Name
see below

Sub Macro4 ()
Application.ScreenUpdating = False
Set mysheets = Sheets(Array("Com;In;Av"))
For Each ws In mysheets
ws.Activate
Range("A6:B6").Copy Range("A7") 'note that Copy can specify a target
For j = 3 To 6
Cells(7, j).FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Next j
Next ws
End Sub

However in column E the calc is a division as below
Range("E5").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("E6").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("E7").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"

And I don't know how to fit this into your code?

--
Many Thanks

Sue


Bernard Liengme said:
Hi Sue,
There is no need to select a cell before you do something to it -- took
me a
while to grasp this concept!
The macro below does what the first 16 lines of your code does. As you
progress, the code saving will increase.

Sub Macro4()
Application.ScreenUpdating = False
Set mysheets = Sheets(Array("com", "in", "av"))
For Each ws In mysheets
ws.Activate
Range("A6:B6").Copy Range("A7") 'note that Copy
can
specify a target
For j = 3 To 6
Cells(7, j).FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Next j
Next ws
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Sue said:
Hi All

I have just used the macro recorder and was wondering if there is an
easier
and less complicated way than the code below

Sub Macro4()
Application.ScreenUpdating = False
Sheets("Com;In;Av").Select
Range("A6:B6").Select
Selection.Copy
Range("A7").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E7").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/d)"
Range("F7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A9:B9").Select
Selection.Copy
Range("A10").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C10").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D10").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E10").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F10").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("F10").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A11:B11").Select
Selection.Copy
Range("A13").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C13").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D13").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E13").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F13").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("F13").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A15:B15").Select
Selection.Copy
Range("A16").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C16").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D16").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E16").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F16").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A18:B18").Select
Selection.Copy
Range("A19").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C19").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D19").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("D19").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E19").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F19").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E7").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("A21:B21").Select
Selection.Copy
Range("A22").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C22").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D22").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E22").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F22").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A24:B24").Select
Selection.Copy
Range("A25").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C25").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D25").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E25").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F25").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A27:B27").Select
Selection.Copy
Range("A28").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C28").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D28").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E28").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F28").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E29").Select
ActiveWindow.SmallScroll Down:=19
Range("A30:B30").Select
Selection.Copy
Range("A31").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C31").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D31").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E31").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F31").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A33:B33").Select
Selection.Copy
Range("A34").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C34").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D34").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E34").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F34").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A36:B36").Select
Selection.Copy
Range("A37").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E37").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F37").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"


Range("A39:B39").Select
Selection.Copy
Range("A40").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D40").Select
ActiveCell.FormulaR1C1 = "=sum"
Range("D40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C/R[-1]C)"
Range("F40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A42:B42").Select
Selection.Copy
Range("A43").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[1]C[-2]/R[1]C[-1])"
Range("E43").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F43").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A45:B45").Select
Selection.Copy
Range("A46").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C46").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("D46").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("E46").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F46").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A48:B48").Select
Selection.Copy
Range("A49").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C49").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D49").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E49").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F49").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A51:B51").Select
Selection.Copy
Range("A52").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C52").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("D52").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("E52").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F52").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("A54:B54").Select
Selection.Copy
Range("A55").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C55").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("D55").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("E55").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F55").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A57:B57").Select
Selection.Copy
Range("A58").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C58").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("D58").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C+R[-1]C)"
Range("E58").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/RC[-1])"
Range("F58").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("E59").Select

Range("A7:F7").Select
Selection.Copy

Range("A70").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A10:F10").Select
Application.CutCopyMode = False
Selection.Copy
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to use Solver in Excel VBA 1
how do I write a loop macro? 9
works in 2007 but not in 2010 0
Autoamate Concatenate Function 3
Different Results from the Same Macro 3
Advanced Filter works in recorder but not while running macro 5
Help again!!! 1
Help merging two VBA codes 2

Top