1

I have code that very simply when something is entered into a column, it selects all of the worksheets and copies it into those worksheets in the same column in the same row. Why then does the code seemingly keep running until it overflow's and kills itself.

How do I stop it after it pastes the value into every single worksheet?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Const cCol As String = "A"
Const fRow As Long = 2

Dim mnths As Long
Dim crg As Range

    Set crg = Columns(cCol).Resize(Rows.Count - fRow + 1).Offset(fRow - 1)
    Dim irg As Range: Set irg = Intersect(crg, Target)
    
        If Not irg Is Nothing Then
        irg.Copy
        Sheets(Array("Statistics", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")).Select
        irg.PasteSpecial xlPasteValues
    Else

    End If
    
End Sub
Drawleeh
  • 297
  • 1
  • 10
  • 2
    https://stackoverflow.com/a/13861640/7599798 – FunThomas Dec 21 '21 at 16:37
  • If you cause a worksheet change in a `worksheet_change` event, you end up in an infinite loop of `worksheet_change` events. To avoid this, turn off events before making any worksheet changes in the `worksheet_change` event - `Application.EnableEvents = False`. And don't forget to turn them back on when you're done - `... = True` – Toddleson Dec 21 '21 at 17:01
  • @Chris Neilsen: This code fails due to the issue in the suggested answer. But OP wants to write the values of the cells of a range to the exact same cells in 13 other worksheets, and if none of them is the worksheet containing this code, the answer has nothing to do with it. – VBasic2008 Dec 21 '21 at 18:15
  • What is the name of the worksheet containing this code? – VBasic2008 Dec 21 '21 at 18:18
  • @vbasic2008 The paste/copy is to the same sheet/range `irg` as the code, the array select makes no difference. Although you can group sheets manually with select and input values to them all at once I don't think you can in VBA. Maybe a loop with value assignment would be the solution. – CDP1802 Dec 21 '21 at 19:53
  • @CDP1802: IMHO, the `paste` is an 'honest' mistake. OP obviously wanted to do something like `Selection.PasteSpecial` which wouldn't work. So the suggested link is the wrong reason for closing the question. – VBasic2008 Dec 21 '21 at 20:53
  • 1
    @vbasic2008 I agree, changing EnableEvents will stop the endless loop but it won't copy the change to all the sheets. I think it should be re-opened. – CDP1802 Dec 21 '21 at 21:10
  • @vbasic Without the copy it works using the ActiveCell like `irg.Select : ActiveCell = irg.Value2`. – CDP1802 Dec 21 '21 at 22:21
  • Wow, unbelievable. Thanks. It works for a single range e.g. you loop the areas and in the loop you do: `Range(arg.Address).PasteSpecial`. – VBasic2008 Dec 21 '21 at 22:38
  • 1
    I do have to say both parts were useful, initially I hadn't understood why it was crashing as I had forgotten to add application.EnableEvents but the further conversation regarding the paste also helped me to implement the paste feature so thank you all. – Drawleeh Dec 22 '21 at 09:26

0 Answers0