fpsmaintenance
New Member
- Joined
- Jan 12, 2023
- Messages
- 3
- Office Version
- 365
- Platform
- Windows
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.
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.
VBA Code:
'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