Worksheet_Change

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

rollis13 · Dec 26th 2020

You need to substitute the 5 lines of code in section called: ' Loop through other sheets plus adding the "Dim ..." line.

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

rollis13 · Dec 27th 2020

Yes, you missed one of the five lines, the first, this one:

Code

For Each ws In Worksheets

was to substituted with:

Code

For Each WorksheetName In Array("BATHROOM", " BATHROOM2", " BATHROOM3")

you just added it.

As you can see in the section "' Loop through other sheets and make the same changes" at the moment you have two 'For' and only one 'Next'. Doing so VBE will non compile the code.

**royUK** · Dec 27th 2020

Vnaza112

Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

How to use code tags

Note: no apostrophe in the tags, just used for demonstration here.

['code]

your code goes between these tags

['/code]

Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.

Thanks.

**royUK** · Dec 27th 2020

rollis13

You should know about code tags. Please use the correct tags in future!

rollis13 · Dec 27th 2020

royUK Sorry, I was pointing out a single line of code and didn't think it was nessary to use Code Tag. I'll be more careful next time.

**royUK** · Dec 27th 2020

Try this

Code

Option Explicit
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
Dim WorksheetName As Variant
Dim lCalc As Long


With Application
lCalc = .Calculation
If lCalc <> 4105 Then .Calculation = 4105        ''/// calculation is not set to Manual
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
On Error GoTo exit_err




'    See if updated cell in our Title range
Set rng = Intersect(Target, Range("B2:AG2"))
If rng Is Nothing Then Exit Sub


'   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 WorksheetName In Array("BATHROOM", " BATHROOM2", " BATHROOM3")
If WorksheetName <> cws.Name Then
Worksheets(WorksheetName).Range(addr) = chg
End If
Next WorksheetName
Next cell
End
exit_err:
.Calculation = lCalc
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
End With


End Sub

Display More

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!

**royUK** · Dec 28th 2020

What is not working?

Vnaza112 · Dec 28th 2020

The code you sent in reply #8

**royUK** · Dec 28th 2020

That's not much help. What error are you getting? It's basically your code.

It would probably help if you attached the workbook

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

Worksheet_Change

Participate now!

Share

Tags