Home Summary Sheet That Updates Source Sheets
Reply: 0

Summary Sheet That Updates Source Sheets

user1580
1#
user1580 Published in July 15, 2018, 9:18 pm

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.31227 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO