Simplify code

[Saoirse]

Is there a way to simplify this code? I have 30 employees and doing this for each one, while is just a lot of copy and pasting, seems time consuming. Is there any way to make it shorter?

Dim NextRow As Integer

NextRow = Sheets("Allie").Cells(Rows.Count, 1).End(xlUp).Row + 1

Sheets("Allie").Range("A" & NextRow) = Format(Now - 1, "MM-DD-YYYY")
Sheets("Allie").Range("B" & NextRow) = WorksheetFunction.CountIf(Sheets("Adjustments").Range("G:G"), "E457038")
Sheets("Allie").Range("C" & NextRow) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_A1")
Sheets("Allie").Range("D" & NextRow) = Sheets("Allie").Range("C" & NextRow) * 8
Sheets("Allie").Range("E" & NextRow) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_B1")
Sheets("Allie").Range("F" & NextRow) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_B2")
Sheets("Allie").Range("G" & NextRow) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_B3")
Sheets("Allie").Range("H" & NextRow) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_B4")
Sheets("Allie").Range("J" & NextRow) = Sheets("Allie").Range("I" & NextRow) * 7
Sheets("Allie").Range("K" & NextRow) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_E")
Sheets("Allie").Range("L" & NextRow) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_C")
Sheets("Allie").Range("N" & NextRow) = Sheets("Allie").Range("M" & NextRow) * 3
Sheets("Allie").Range("O" & NextRow) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_H")
Sheets("Allie").Range("P" & NextRow) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_R")
Sheets("Allie").Range("Q" & NextRow) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_K1")
Sheets("Allie").Range("R" & NextRow) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_K2")
Sheets("Allie").Range("T" & NextRow) = Sheets("Allie").Range("S" & NextRow) * 4
Sheets("Allie").Range("U" & NextRow) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_X")
Sheets("Allie").Range("V" & NextRow) = Sheets("Allie").Range("U" & NextRow) * 3


Thank you to anyone that can help.

 

[Michael M]

Is it for all sheets in the workbook ?
If not, what sheets are to be excluded ?

 

[Saoirse]

There are three sheets in the workbook that are not included in this workbook. Adjustments, Compilation, and timing. The rest are titled the employees name.

 

[Michael M]

UNTESTED, but I would also consider reducing your ranges, eg, "G:G" is over 1 million rows, I'd suggestr an arbitary figure that will cover you data maybe "G1:G1000"
and do you really have data down to "E457038"

Sub MM1()
Dim lr As Long, ws As Worksheet
For Each ws In Worksheets
    If ws.Name <> "Adjustments" And ws.Name <> "Compilation" And ws.Name <> "timing" Then
        With ws
            lr = .Cells(Ro.Count, 1).End(xlUp).Row + 1
            .Range("A" & lr) = Format(Now - 1, "MM-DD-YYYY")
            .Range("B" & lr) = WorksheetFunction.CountIf(Sheets("Adjustments").Range("G:G"), "E457038")
            .Range("C" & lr) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_A1")
            .Range("D" & lr) = .Range("C" & lr) * 8
            .Range("E" & lr) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_B1")
            .Range("F" & lr) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_B2")
            .Range("G" & lr) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_B3")
            .Range("H" & lr) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_B4")
            .Range("J" & lr) = .Range("I" & lr) * 7
            .Range("K" & lr) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_E")
            .Range("L" & lr) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_C")
            .Range("N" & lr) = .Range("M" & lr) * 3
            .Range("O" & lr) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_H")
            .Range("P" & lr) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_R")
            .Range("Q" & lr) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_K1")
            .Range("R" & lr) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_K2")
            .Range("T" & lr) = .Range("S" & lr) * 4
            .Range("U" & lr) = WorksheetFunction.CountIfs(Sheets("Adjustments").Range("G:G"), "E457038", Sheets("Adjustments").Range("F:F"), "DB_X")
            .Range("V" & lr) = .Range("U" & lr) * 3
        End With
    End If
Next ws
End Sub

 

[Saoirse]

So the reason I did the G:G is because the data runs over 10,000 rows.
And the E457038 corelates with a specific person. That person is given a number and my report that I put into the adjustments tab and run daily only recognizes the person based on that number. so there are 30 different numbers too sift through within those 10,000 rows. which the formulats match up with specific steps then put onto a chart specifically for that one person, but I need to have it run for 30 people with 30 different numbers like that.

I hope I am explaining that correctly. It makes sense in my head but I am not sure I am making it clear.