CODEWITHSUNDEEP
excelvba
cjvdg
cjvdg

Reputation: 533

VBA Excel - Update one column in different sheet simultaneously

May I ask if how can I update a column in different sheets simultaneously? So the thing is I have multiple sheets with the same structure and I need to update the column P with 1 or 0.

Another thing is that I have a delete function that deletes rows simultaneously. It works fine and deletes rows at the same time. Can I use this for my concern? But rather than delete, codes should be modified with the process of updating.

Sub del_stud()

    LRN = ThisWorkbook.Sheets("HOME").Range("K11").value
    
    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", _
    "CERTIFICATION"))
    
        With ws.Cells(8, 3).CurrentRegion
            .AutoFilter 2, LRN
            .Offset(1).EntireRow.Delete
            ws.AutoFilterMode = False
        End With
    Next ws
    
    ThisWorkbook.Sheets("HOME").Range("K11").value = ""

End Sub

Upvotes: 0

Views: 69

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

Sub UpdateAllSheets(rngAddress as String, valueToSet)

    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", "CERTIFICATION"))
        ws.range(rngAddress).value = valueToSet
    Next ws
End Sub

Example call to set P5 to 1 on each sheet:

UpdateAllSheets "P5", 1

Upvotes: 2

Related Questions