Excel VBA code update to consolidate multiple sheets in master sheet and edit from master sheet

[fpsmaintenance]

Hi All,

I currently have the following VBA code on my master sheet "Summary" that consolidates the rows from multiple worksheets (slave) into this single master sheet.

I have modified the code to include items like scrolling to the top of the page, removing and adding filters and excludes a worksheet that I use to create reports.

I am looking to see if there is a way to add to or modify the code to allow me to modify the slave worksheets from the single master worksheet.

At present, the code simply copies the data from the slave worksheets into the master worksheet, it does not actually link to the data in the slave worksheet. So if I make a change in the master worksheet, it does not affect the relevant slave worksheet and the changes that I manually make in the master worksheet revert back to the data in the slave worksheet when I run the code again.

'This consolidates data from the range A5:T10000 for every tab except the one it's part of.

Private Sub Worksheet_Activate()

    'This turns off all filters on the summary page to correctly pull and consolidate the data from the other sheets
    
    If Worksheets("Summary").FilterMode Then
        
        Worksheets("Summary").ShowAllData
        
    End If

    Dim wrkSheet As Worksheet
    Dim rngCopy As Range
    Dim lngPasteRow As Long
    Dim strConsTab As String
     
    strConsTab = ActiveSheet.Name 'Consolidation sheet tab name based on active tab.
        
    'This clears the contents of the summary page to allow for new content to be added
    
    If Sheets(strConsTab).Cells(Rows.Count, "A").End(xlUp).Row >= 6 Then
        
        Sheets(strConsTab).Range("A5:V" & Cells(Rows.Count, "A").End(xlUp).Row).ClearContents
        
    End If
         
    Application.ScreenUpdating = False
            
    For Each wrkSheet In ActiveWorkbook.Worksheets
    
        'This excludes the "Report" worksheet from being included in the summary sheet
        
        If wrkSheet.Name <> "Report" Then
                   
        'This turns off all filters in the worksheet to display all data
        
            If wrkSheet.FilterMode Then
                
                wrkSheet.ShowAllData
        
            End If
            
            'This pulls the data from all the other worksheets into the summary page
            
            If wrkSheet.Name <> strConsTab Then
                    
                Set rngCopy = wrkSheet.Range("A5:U10000")
                lngPasteRow = Sheets(strConsTab).Cells(Rows.Count, "A").End(xlUp).Row + 1
                rngCopy.Copy Sheets(strConsTab).Range("A" & lngPasteRow)
                Application.CutCopyMode = False
                
            End If
        
        End If
                      
    Next wrkSheet

 

[dmt32]

I am looking to see if there is a way to add to or modify the code to allow me to modify the slave worksheets from the single master worksheet.

Hi welcome to forum

Excel is optimized for data analysis and calculation and does not function as a database management system, like Access.

You store data on a separate “slave” worksheet but each sheet (Access calls them data tables) you want related to your summary sheet so any updates are reflected back?

Using a database application like Access would be straight forward as the tables can all be related – Using Excel is in same manner is somewhat challenging but as an idea there may be a clunky sort of way that might work for you

• when copying the worksheet data to your summary table, you also copy the relevant worksheet name & record row using helper cells
• to update a record in its own sheet after changes you make in the summary sheet, you copy the target record & and using the worksheet name & record row in helper cells, you paste the entire row back correct sheet / record row.

To try the idea place BOTH codes in your worksheet Summary CODE PAGE

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim DataRange   As Range
    Dim ws          As Worksheet
    Dim RecordRow   As Long
   
     On Error GoTo myerror
    
    'get data range ecluding helper cells
    With Me.Range("A1").CurrentRegion
        Set DataRange = .Offset(1).Resize(.Rows.Count - 1, .Columns.Count - 2)
    End With
   
    If Not Intersect(Target, DataRange) Is Nothing Then
        Application.EnableEvents = False
        'worksheet name
        Set ws = Worksheets(Cells(Target.Row, "V").Text)
        'record row
        RecordRow = Cells(Target.Row, "W").Value
        'copy & paste row to worksheet range
        DataRange.Rows(Target.Row - 1).Copy ws.Cells(RecordRow, 1)
    End If
   
myerror:
    Application.EnableEvents = True
End Sub


Private Sub Worksheet_Activate()
    Dim wrkSheet            As Worksheet, wsConsTab As Worksheet
    Dim rngCopy             As Range
    Dim lngPasteRow         As Long, CopyRows As Long, lr As Long
   
    'Consolidation sheet tab name
    Set wsConsTab = ActiveSheet
   
    On Error GoTo myerror
   
    'turn events off whilst summary sheet data updated
     With Application
        .EnableEvents = False: .ScreenUpdating = False
     End With
   
    With wsConsTab
        'This turns off all filters on the summary page to correctly pull and consolidate the data from the other sheets
        If .FilterMode Then .ShowAllData
        'This clears the contents of the summary page to allow for new content to be added
        lr = .Cells(.Rows.Count, "A").End(xlUp).Row
        If lr >= 6 Then .Range("A5:W" & lr).ClearContents
    End With
   
    Application.ScreenUpdating = False
   
     'This pulls the data from all the other worksheets into the summary page
    For Each wrkSheet In ActiveWorkbook.Worksheets
       
        'This excludes the "Report" and Summary worksheets
        If wrkSheet.Name <> wsConsTab.Name And wrkSheet.Name <> "Report" Then
           
            With wrkSheet
            'This turns off all filters in the worksheet to display all data
                If .FilterMode Then .ShowAllData
                'get number rows to copy
                CopyRows = .Cells(.Rows.Count, "A").End(xlUp).Row
                If CopyRows < 5 Then CopyRows = 5
                'set copy range
                Set rngCopy = .Range("A5:U" & CopyRows)
            End With
           
            With wsConsTab
                ' next blank row
                lngPasteRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
                'copy all data & paste to summary sheet
                rngCopy.Copy .Range("A" & lngPasteRow)
                'add sheet name
                .Cells(lngPasteRow, "V").Resize(CopyRows - 4).Value = wrkSheet.Name
                'add row number series starting from Row 5
                With .Cells(lngPasteRow, "W")
                    .Value = 5
                    .AutoFill Destination:=.Resize(CopyRows - 4), Type:=xlFillSeries
                End With
            End With
           
        End If
       
        'clear object
        Set rngCopy = Nothing
       
    Next wrkSheet
   
myerror:
    With Application
        .EnableEvents = True: .ScreenUpdating = True
     End With
End Sub

Not very elegant & I have only lightly tested the concept which probably will need further work but it is just an idea that you may be able to develop to meet your specific project need.

Dave

 

[dmt32]

Sorry, I just spotted that I did not before posting, amend the worksheet_change event to start from my test row to your actual data start row (row 5)
Also, I have as further thought, included a line you can use that just updates a single cell rather than whole row.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim DataRange   As Range
    Dim ws                  As Worksheet
    Dim RecordRow   As Long
   
     On Error GoTo myerror
    
    'assumes row 4 is the header row
    With Me.Range("A4").CurrentRegion
        ' get data range excluding helper cells
        Set DataRange = .Offset(1).Resize(.Rows.Count - 1, .Columns.Count - 2)
    End With
   
    If Not Intersect(Target, DataRange) Is Nothing Then
        Application.EnableEvents = False
        'worksheet name
        Set ws = Worksheets(Cells(Target.Row, "V").Text)
        'record row
        RecordRow = Cells(Target.Row, "W").Value
       
        'copy row & paste to worksheet range
        DataRange.Rows(Target.Row - 4).Copy ws.Cells(RecordRow, 1)
       
        'or update individual cells only
       'ws.Cells(RecordRow, Target.Column).Value = DataRange.Cells(Target.Row - 4, Target.Column).Value
    End If
   
myerror:
    Application.EnableEvents = True
End Sub

Dave

 

[fpsmaintenance]

HI Dave,

That is great thanks for having a look into this. I will try this out when I get a chance this week and hopefully it works!

Cheers Jacques

 

[dmt32]

That is great thanks for having a look into this. I will try this out when I get a chance this week and hopefully it works!

Hi
glad suggestion may help you - as said, I only lightly tested the idea & will need to be adapted to meet your specific project need but hopefully, idea will work for you but if have difficulty, post back.

Appreciate your feedback

Dave

 

[fpsmaintenance]

Hi Dave,

I have finally had a chance to test the code. I think I understand vaguely what you are trying to do with the additional columns to give a reference to the original worksheets (this coding is far beyond my experience so I am surprised I made it this far!).

I have a few questions:

1) Is there any code that I need to put into the "slave" worksheets?
2) When I try and make a change in the first item on the summary sheet, all it does on the slave sheet is copy the title row into the next row:

3) If i try and make a change to any other rows, nothing changes in the slave sheet and as soon as I go back to the master summary sheet it simply reverts back to normal.

Can I send through a copy of the worksheet to maybe give an idea of the data that I am pulling and what I am trying to update? THe main things that I would like to do is update the status of items as they progress (e.g. new issue to resolved issue) and fill in any other details without having to scroll to the specific slave sheet but rather do a quick update on the summary and move on.

Any help is much appreciated, thanks again!

Cheers Jacques

 

[dmt32]

Hi
As in most cases when suggestions are made, code provide includes a bit of guess about a workbook structure / layout.

If having problems adapting the solution to meet your specific need then helpful if you could place a copy of the workbook with dummy data in a file sharing site like dropbox & provide a link to it

Dave