Short macro code and will be continue next row and sheet

[Closed]
Report
Posts
4
Registration date
Friday July 31, 2015
Status
Member
Last seen
August 1, 2015
-
Hi Everyone, i have macro code that calculate excel sheets and paste answer in dashboard row, each new excel sheet data paste in new row after calculation.
i want to short this code and will be working lot of sheet and row. i means it continue when any new sheet create its done calculation and paste in next row. Here is code:

Sub Macro1()
'
' Macro1 Macro
'


'
Range("A5").Select
ActiveCell.FormulaR1C1 = "4-16 - 4-22"
Range("B5").Select
ActiveCell.FormulaR1C1 = "1st"
Range("C5").Select
ActiveCell.FormulaR1C1 = "=AVERAGEIFS('adt4-16 - 4-22'!C16, 'adt4-16 - 4-22'!C16, "">301"",'adt4-16 - 4-22'!C16, ""<480"")"
Range("D5").Select
ActiveCell.FormulaR1C1 = "=COUNTIFS('adt4-16 - 4-22'!C16,"">""&301,'adt4-16 - 4-22'!C16,""<""&480)"
Range("E5").Select
ActiveCell.FormulaR1C1 = "=(R2C3-RC3)*(R1C4*RC4)"
Range("F5").Select
ActiveCell.FormulaR1C1 = "=AVERAGEIFS('adt4-16 - 4-22'!C16, 'adt4-16 - 4-22'!C16, "">=1"",'adt4-16 - 4-22'!C16, ""<300"")"
Range("G5").Select
ActiveCell.FormulaR1C1 = "=COUNTIFS('adt4-16 - 4-22'!C16,"">=""&1,'adt4-16 - 4-22'!C16,""<""&300)"
Range("H5").Select
ActiveCell.FormulaR1C1 = "=(R2C3-RC6)*(R1C4*RC7)"
Range("I5").Select
ActiveCell.FormulaR1C1 = "=AVERAGE('adt4-16 - 4-22'!C16)"
Range("J5").Select
ActiveCell.FormulaR1C1 = "=COUNTIFS('adt4-16 - 4-22'!C16,"">=""&1)"
Range("K5").Select
ActiveCell.FormulaR1C1 = "=(R2C3-RC9)*(R1C4*RC10)"


Range("A6").Select
ActiveCell.FormulaR1C1 = "4-23 - 4-29"
Range("B6").Select
ActiveCell.FormulaR1C1 = "2nd"
Range("C6").Select
ActiveCell.FormulaR1C1 = "=AVERAGEIFS('adt4-23 - 4-29'!C16, 'adt4-23 - 4-29'!C16, "">301"",'adt4-23 - 4-29'!C16, ""<480"")"
Range("D6").Select
ActiveCell.FormulaR1C1 = "=COUNTIFS('adt4-23 - 4-29'!C16,"">""&301,'adt4-23 - 4-29'!C16,""<""&480)"
Range("E6").Select
ActiveCell.FormulaR1C1 = "=(R[-4]C3-RC3)*(R1C4*RC4)"
Range("F6").Select
ActiveCell.FormulaR1C1 = "=AVERAGEIFS('adt4-23 - 4-29'!C16, 'adt4-23 - 4-29'!C16, "">=1"",'adt4-23 - 4-29'!C16, ""<300"")"
Range("G6").Select
ActiveCell.FormulaR1C1 = "=COUNTIFS('adt4-23 - 4-29'!C16,"">=""&1,'adt4-23 - 4-29'!C16,""<""&300)"
Range("H6").Select
ActiveCell.FormulaR1C1 = "=(R[-4]C3-RC6)*(R1C4*RC7)"
Range("I6").Select
ActiveCell.FormulaR1C1 = "=AVERAGE('adt4-23 - 4-29'!C16)"
Range("J6").Select
ActiveCell.FormulaR1C1 = "=COUNTIFS('adt4-23 - 4-29'!C16,"">=""&1)"
Range("K6").Select
ActiveCell.FormulaR1C1 = "=(R2C3-RC9)*(R1C4*RC10)"

End Sub

First 26 row of code calculate Sheet2 and paste in row 5 and next 26 do it for next sheet3 and row 6. and so on.

Please help me to make it short, continue for new sheets/row and use ful.

Thanks advance for your help.