Posts by Vnaza112

Vnaza112 · Dec 28th 2020

Ok, this is the original file, and I pasted the VBA code to sheets "BATHROOM" and "BATHROOM2"

When I make any changes in my range "B2:AG2" in either sheets the change occurs on all sheets.

Once again thank you for your time

Vnaza112 · Dec 28th 2020

The code you sent in reply #8

Vnaza112 · Dec 27th 2020

royUK, Thank you so much for taking the time to help me. I just do not get it. I tried it, but it is not working.

I will not take up any more of your time, but I really want thank you for what you have given me to this point. It means a lot.

Happy Holidays and I wish you a Safe and Healthy New Years!

Vnaza112 · Dec 27th 2020

Thank you for your quick response.

This is what I did and I get an error in the highlighted and underlined in red spot below

Code

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
Dim WorksheetName As Variant


For Each WorksheetName In Array("BATHROOM", " BATHROOM2", " BATHROOM3")
If WorksheetName <> cws.Name Then
ThisWorkbook.Worksheets(WorksheetName).Range(addr) = chg
End If
Next WorksheetName
Next cell


Application.ScreenUpdating = True
Application.EnableEvents = True


End Sub

Display More

Vnaza112 · Dec 26th 2020

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:

Code

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

Display More

2) AND this is the suggestion I got to make changes only in a group of worksheet:

Code

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