How to simplify this code?

[klarowe]

I am trying to develop a code that will first check if a sheet is visible and if so, it will "copy" the value from a specific cell to a specific cell on another sheet.
Here is the code that is currently working:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 
    If Sheets("Bearing (1)").Visible Then Sheets("Index").Range("D12").Value = Sheets("Bearing (1)").Range("D2").Value
    If Sheets("Bearing (2)").Visible Then Sheets("Index").Range("D13").Value = Sheets("Bearing (2)").Range("D2").Value
End Sub

Now this code has to be repeated 15 times (and possibly more in the future). So I would like to give my fingers a break if possible and simplify it to a For i = 1 to 15 type situation... but I can't seem to figure it out. Please notice that the Index sheet Range increases by 1 row for each bearing sheet (i+11). But the Bearing sheet range is always the same ("D2")

The other issue is that if I do type it all out (pain, but I wanted to at least try it), it seems to freeze up the computer with a never-ending code. Pressing ESC will force end the code, but obviously that isn't right.. lol

Any help is appreciated.

 

[iliace]

So something like this doesn't work?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Dim i As Long
  
  For i = 1 To 15
    If Sheets("Bearing (" & i & ")").Visible Then
      Sheets("Index").Range("D12").Offset(i - 1, 0).Value = _
              Sheets("Bearing (" & i & ")").Range("D2").Value
    End If
  Next i
End Sub

 

[klarowe]

I had something similar to that but with (i+11) instead of the offset and that was causing it to freeze up. I will give that exact code a shot tomorrow.

 

[iliace]

It would have to have been like this:

Range("D" & 12 + i - 1)

to work correctly. But anyway, if it still doesn't work, figure out where it encounters the problem, either using break point, stepping through the code, looking at the incomplete result, or some combination of those.

 

[iliace]

It would have to have been like this:

Range("D" & 11 + i)

to work correctly. But anyway, if it still doesn't work, figure out where it encounters the problem, either using break point, stepping through the code, looking at the incomplete result, or some combination of those.

 

[klarowe]

I tried the code and it puts it into the neverending cycle until I press ESC. Then if I hit debub, it just highlights "End If" which tells me nothing. I'm not really sure how to use the break point or how to step through the code....

 

[klarowe]

I also tried simplifying it slightly by using the "VBA" sheet name rather than the displayed sheet name and it still goes into the neverending code.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Dim i As Long
  
  For i = 6 To 20
    If Sheets(i).Visible Then
      Sheets("Index").Range("D12").Offset(i - 6, 0).Value = Sheets(i).Range("D2").Value
    End If
  Next i
        
End Sub

 

[Weaver]

It looks to me like you might be changing the active sheet within a worksheet change event, so it will end up calling itself.

To get out of this you need to disable events, but this can cause problems if the code errors, so you need to have a trap that ensures events are re-enabled in this case

Try:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Application.EnableEvents = False
  Dim i As Long
  On Error GoTo safeExit
  For i = 6 To 20
    If Sheets(i).Visible Then
      Sheets("Index").Range("D12").Offset(i - 6, 0).Value = Sheets(i).Range("D2").Value
    End If
  Next i
safeExit:
Application.EnableEvents = True
End Sub

See if that helps

 

[klarowe]

I never thought of that... but it makes perfect sense.

Everything is now working great. Thanks for the help!!!!!