0

this is my first post here. I have a workbook that contains worksheets, each with a chart for one of our locations. Each chart allows the user to set the desired day of the week (DOW). Additionally, there's a master table that hosts all the data and it also allows for the user to select specific DOW data.

I have five charts and the master table each with a DOW cell. What I'm trying to do is update all of the other 5 DOW cells simultaneously every time one of the DOW cells are changed by the user, regardless of which sheet it's changed on. I'm hoping this is possible.

I created Private Subs for each worksheet with the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A5")) Is Nothing Then

Call LOC1DOW

End If

End Sub

The function it's calling is:

Sub LOC1DOW()

Application.ScreenUpdating = False

Worksheets("LOC1").Range("A5").Copy
Worksheets("LOC2").Range("A5").PasteSpecial Paste:=xlPasteValues
Worksheets("LOC3").Range("A5").PasteSpecial Paste:=xlPasteValues
Worksheets("LOC4").Range("A5").PasteSpecial Paste:=xlPasteValues
Worksheets("LOC5").Range("A5").PasteSpecial Paste:=xlPasteValues
Worksheets("LOC6").Range("A5").PasteSpecial Paste:=xlPasteValues
Worksheets("P1").Range("A5").Select

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

I put a modified version of this in each worksheet and ran a test and of course it crashed my file, because it's a closed loop that I didn't recognize in time. I know I took a pretty basic approach to this problem, but I just don't know VBA enough to understand how to accomplish this. Any and all help is much appreciated!!

braX
  • 11,506
  • 5
  • 20
  • 33
jgett
  • 1
  • Use the `Workbook_SheetChange` event instead... pretty similar, but occurs when a change happens *on any sheet*. To avoid Excel crashing, you can toggle events off and on with `Application.EnableEvents` when updating the value on each sheet. – BigBen May 27 '20 at 19:20
  • Thanks, BigBen. I considered that, but I need this to apply to specific worksheets only. There are plenty of worksheets that need to be isolated from these changes happening. I'll try to turn off events and see if that helps. – jgett May 27 '20 at 19:28
  • It will help - because otherwise changing the value causes the event to fire again and again and again.... https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure – BigBen May 27 '20 at 19:28
  • 1
    That did it! Thanks so much, BigBen. Really appreciate the quick and helpful feedback. – jgett May 27 '20 at 19:42

0 Answers0