VBA Excel - Add same value to columns in different sheet

Just want to ask if how can I shorten the codes below? The codes work fine, but I just want to know if there is a way to shorten it since I'll be using the codes in N-Q1 sheet in other quarter sheets (N-Q1, N-Q2, N-Q3, N-Q4, JK-Q1, etc.). Note that, the quarter sheets have the same structure or column (column 16) to be updated, while the STUDENTS_INFO sheet is in column 20.

STUDENTS INFO sheets:

Set ws = ActiveWorkbook.Worksheets("STUDENTS_INFO")  lastRow = ws.Cells(Rows.Count, "C").End(xlUp).Row                For r = 9 To lastRow                    If ws.Cells(r, 3) = CStr(ThisWorkbook.Sheets("HOME").Range("K11").value) Then              If ws.Cells(r, 20) = "0" Or ws.Cells(r, 20) = "" Then                  ws.Cells(r, 20) = "1"                  Debug.Print "STUDENTS: " & ws.Cells(r, 3) & "  Verified!"              Else                  Debug.Print "STUDENTS: " & ws.Cells(r, 3) & "  Already Verified!"              End If          End If            Next r  

N-Q1 code: Quarter Sheets (N-Q1, N-Q2, N-Q3, N-Q4, JK-Q1, etc.)

    grd = ThisWorkbook.Sheets("HOME").Range("K16").value      qrt = CStr(ThisWorkbook.Sheets("HOME").Range("K17").value)                    If grd = "Nursery" Then          ws_output = "N" + "-" + qrt      ElseIf grd = "Junior Kinder" Then          ws_output = "JK" + "-" + qrt      ElseIf grd = "Senior Kinder" Then          ws_output = "SK" + "-" + qrt      End If            Set ws = ActiveWorkbook.Worksheets(ws_output)      lastRow = ws.Cells(Rows.Count, "C").End(xlUp).Row                For s = 9 To lastRow                    If ws.Cells(s, 3) = CStr(ThisWorkbook.Sheets("HOME").Range("K11").value) Then              If ws.Cells(s, 16) = "0" Or ws.Cells(r, 16) = "" Then                  ws.Cells(s, 16) = "1"                  Debug.Print "GRADES: " & ws.Cells(s, 3) & "  Verified!"              Else                  Debug.Print "GRADES: " & ws.Cells(s, 3) & "  Already Verified!"              End If          End If            Next s  

Note that, this code will only work depends on the value of grd and qrt.

I'm thinking of using the code below as a start.

For Each ws In Sheets(Array("STUDENTS_INFO", "N-Q1", "N-Q2", "N-Q3", "N-Q4", "N-D", _  "JK-Q1", "JK-Q2", "JK-Q3", "JK-Q4", "JK-D", "SK-Q1", "SK-Q2", "SK-Q3", "SK-Q4", "SK-D"))                With ws.Cells(8, 3).CurrentRegion              .AutoFilter 2, LRN              ws.AutoFilterMode = False          End With    Next ws  

https://stackoverflow.com/questions/66594947/vba-excel-add-same-value-to-columns-in-different-sheet March 12, 2021 at 02:00PM