Need Help to reduce Code

[isixdos1]

I am a novice with code. Mostly record and adjust manual as needed. I have this code and it repeats several times. Is there any way to reduce the number of lines, thus speeding the macro up when running?
(Example Code 1)
Range("CK8").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(R8C1:RC1,RC1)=1,SUMIF(R8C1:R999C1,RC1,R8C[-86]:R999C[-86]),"""")"
Range("CL8").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(R8C1:RC1,RC1)=1,SUMIF(R8C1:R999C1,RC1,R8C[-86]:R999C[-86]),"""")"
Range("CM8").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(R8C1:RC1,RC1)=1,SUMIF(R8C1:R999C1,RC1,R8C[-85]:R999C[-85]),"""")"
Range("CN8").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(R8C1:RC1,RC1)=1,SUMIF(R8C1:R999C1,RC1,R8C[-85]:R999C[-85]),"""")"
Range("CO8").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(R8C1:RC1,RC1)=1,SUMIF(R8C1:R999C1,RC1,R8C[-85]:R999C[-85]),"""")"

(Example Code 1 leads into the following)
Sheets("Sheet2").Select
Range("A7").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="443121"
Range("CK6:DU6").Select
Selection.Copy
Sheets("Sheet1").Select
Range("H10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells(10, 7) = "443121"
Sheets("Sheet2").Select
Application.CutCopyMode = False

Range("A7").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="443122"
Range("CK6:DU6").Select
Selection.Copy
Sheets("Sheet1").Select
Range("H11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells(11, 7) = "443122"
Sheets("Sheet2").Select
Application.CutCopyMode = False

Each of these repeat multiple time the above is only examples. What can I do to reduce it?

 

[Ruddles]

Reducing the number of lines of code in a macro doesn't necessarily make it run any faster if it's doing the same amount of actual work. In other words, performing an action on 100 cells takes the same amount of time whether you code 100 separate statements or you use a loop to execute the same statement 100 times.

Having said that, using .Select is time-consuming in itself and is seldom necessary, so perhaps you could change this:-

Range("CK8")[B][COLOR=red].Select
[/COLOR][COLOR=red]ActiveCell[/COLOR][/B].FormulaR1C1 = _
"=IF(COUNTIF(R8C1:RC1,RC1)=1,SUMIF(R8C1:R999C1,RC1,R8C[-86]:R999C[-86]),"""")"

to this:-

Range("CK8").FormulaR1C1 = _
"=IF(COUNTIF(R8C1:RC1,RC1)=1,SUMIF(R8C1:R999C1,RC1,R8C[-86]:R999C[-86]),"""")"

Generally any pair of statements where the first ends with .Select and the second starts with Selection. can be joined in this way, so this:-

Range("A7")[COLOR=red][B].Select[/B][/COLOR]
[COLOR=red][B]Selection.[/B][/COLOR]AutoFilter
[B][COLOR=red]Selection.[/COLOR][/B]AutoFilter Field:=1, Criteria1:="443122"

can be writen as:-

Range("A7").AutoFilter
Range("A7").AutoFilter Field:=1, Criteria1:="443122"

or probably even:-

Range("A7").AutoFilter Field:=1, Criteria1:="443122"

(although you'd need to test this second suggestion to make sure).