1

I'd like to make a summary sheet that, if changed, changes the source sheets it is pulling from. The code I have so far aggregates all of my sheets on the summary sheet on the summary sheet's activation event. I am trying to have all of my other sheets updated on the deactivation event but it does not seem to be working. Here is the code I am working with:

Private Sub Worksheet_Deactivate()

Application.ScreenUpdating = False

Dim tabs As Variant
tabs = Array("BELD", "RMLD", "Pascoag", "Devens", "WBMLP", "Rowely", "AMP", "First Energy", "Dynegy", "APN", "MISC")

For j = 1 To UBound(tabs)

    Sheets(tabs(j)).Select

    Dim rng1 As Range
    Dim Stri As String
        For i = 3 To ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange.Cells.Count).Row
            Stri = ActiveSheet.Cells(i, "A")
            Set rng1 = Worksheets("Summary").Range("A:A").Find(Stri, , xlValues, xlWhole)
            If Not rng1 Is Nothing Then
                Sheets("Summary").Range(rng1.Address).EntireRow.Copy
                ActiveSheet.Range("A" & i).EntireRow.Select
                Selection.Insert Shift:=xlLeft
                ActiveSheet.Range("A" & i + 1).EntireRow.Select
                Selection.Delete Shift:=xlUp
            Else
                MsgBox strSearch & " not found"
            End If
        Next

        ActiveSheet.Range("A" & 1).Select

Next

Application.ScreenUpdating = True

End Sub

I am very new to vba and this is my first post on stackoverflow so if I missed anything just let me know.

Steevp
  • 13
  • 3

1 Answers1

0

When you assign a variant array in that manner, you will end up with a zero-based array. You need to start at j = 0. As your own code currently is, it will never access the BELD worksheet.

Dim tabs As Variant
tabs = Array("BELD", "RMLD", "Pascoag", "Devens", "WBMLP", "Rowely", "AMP", "First Energy", "Dynegy", "APN", "MISC")

For j = 0 To UBound(tabs)
   ....

A more universal method would be using For j = LBound(tabs) To UBound(tabs) which does not matter whether your array is 1 or 0 based as you let each array describe its own properties through the LBound function and UBound function.

A more comprehensive rewrite of your routine would include getting rid of the .Select and .Activate methods and use direct worksheet and cell referencing in its place.

Private Sub Worksheet_Deactivate()
    Dim rng1 As Range
    Dim Stri As String, lr As Long, j As Long, i As Long
    Dim tabs As Variant

    On Error GoTo bm_Safe_exit
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    tabs = Array("BELD", "RMLD", "Pascoag", "Devens", "WBMLP", "Rowely", _
                 "AMP", "First Energy", "Dynegy", "APN", "MISC")

    For j = LBound(tabs) To UBound(tabs)
        With Sheets(tabs(j))
            lr = .Cells.Find(Chr(42), After:=.Cells(1, 1), SearchDirection:=xlPrevious).Row
            For i = 3 To lr
                Stri = .Cells(i, "A").Value
                If CBool(Len(Stri)) Then
                    On Error Resume Next
                    With Me.Range("A:A")
                        Set rng1 = .Find(What:=Stri, After:=.Cells(.Rows.Count), LookIn:=xlValues, LookAt:=xlWhole)
                    End With
                    On Error GoTo bm_Safe_exit
                    If Not rng1 Is Nothing Then
                        'clearing then copy/paste may be better than inserting, pasting and ultimately deleting old row
                        .Rows(i).Clear
                        rng1.EntireRow.Copy _
                            Destination:=.Range("A" & i)
                    Else
                        'maybe copy the data from the sheet back to the summary sheet if this occurs
                        MsgBox Stri & " on " & .Name & " not found on Summary"
                    End If
                End If
            Next
        End With
    Next

bm_Safe_exit:
    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

Since this is in the Summary worksheet's code sheets, the use of Me can be applied to the Summary worksheet object. Once you have set rng1 to the range returned by the find, it is no longer necessary to describe the worksheet it comes from as its Range .Parent property is carried with it.

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

Community
  • 1
  • 1