Good afternoon to all:
I was given the following VBA to make changes to a specific cell range. The VBA works great, but it does the change to all of my worksheet. I was ten given information on how to make the VBA good for only a group of Worksheets, But I do not know how to put it together. I do not know VBA. Some one please help...
1) Below is VBA that makes changes to all worksheets:
Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range Dim rng As Range Dim cws As Worksheet Dim ws As Worksheet Dim addr As String Dim chg As Variant Application.ScreenUpdating = False ' See if updated cell in our Title range Set rng = Intersect(Target, Range("B2:AG2")) If rng Is Nothing Then Exit Sub ' Temporarily disable events so code does not call other Worksheet_Chg procs Application.EnableEvents = False ' Capture current worksheet Set cws = ActiveSheet ' Loop through changed cells For Each cell In rng ' Capture details of change addr = cell.Address 'address of cell chg = cell.Value 'value of cell ' Loop through other sheets and make the same changes For Each ws In Worksheets If ws.Name <> cws.Name Then ws.Range(addr) = chg End If Next ws Next cell Application.ScreenUpdating = True Application.EnableEvents = True End Sub
2) AND this is the suggestion I got to make changes only in a group of worksheet:
Dim WorksheetName As Variant For Each WorksheetName In Array("Sheet4", "Sheet6", "Sheet8") If WorksheetName <> cws.Name Then ThisWorkbook.Worksheets(WorksheetName).Range(addr) = chg End If Next WorksheetName
3) Can Someone please help me put it together?
Thank you so much