helplessnoobatexcel
New Member
- Joined
- Dec 15, 2023
- Messages
- 45
- Office Version
- 365
- Platform
- Windows
Hi guys, I have this VBA code which is meant to check if any changes has been made to the selected sheets that are currently linked to the master sheet and if yes, it would automatically update the master sheet. It also consolidates the selected sheets into a master sheet and removes rows of blanks in the master sheet. (if any) . However, this code gives me multiple errors when I run it. Any kind soul willing to help me run this code on a workbook and give me some pointers on what I can change? It would be greatly appreciated!! I have been stuck on this for nearly 5 days already ;-;.
Dim selectedSheets As Sheets
Dim masterSheet As Worksheet
Private Sub Worksheet_Change(ByVal Target As Range)
'Check if the change occured in one of the selected sheets'
If Not Intersect(Target.Worksheet, selectedSheets) Is Nothing Then
'Call the consolidation function when changes are detected'
ConsolidateAndRemoveBlanks
End If
End Sub
Sub ConsolidateAndRemoveBlanks()
Dim ws As Worksheet
Dim lastRow As Long, i As Long, j As Long
'Check if the master sheet exists, if not, create it'
On Error Resume Next
Set masterSheet = Worksheets("MasterSheet")
On Error GoTo 0
If masterSheet Is Nothing Then
Set masterSheet = Sheets.Add(After:=Sheets(Sheets.Count))
masterSheet.Name = "MasterSheet"
Else
'Clear existing data on the master sheet'
masterSheet.Cells.Clear
End If
'Loop through selected sheets and consolidate data'
For Each ws In ActiveWindow.selectedSheets
'Copy data to master sheet'
ws.UsedRange.Copy masterSheet.Cells(masterSheet.Rows.Count, 1).End(x1Up).Offset(1, 0)
Next ws
'Remove blank rows from the master sheet'
lastRow = masterSheet.Cells(masterSheet.Rows.Count, 1).End(x1Up).Row
For i = lastRow To 1 Step -1
For j = 1 To masterSheet.Columns.Count
If IsEmpty(masterSheet.Cells(i, j)) Then
masterSheet.Rows(i).Delete
Exit For
End If
Next j
Next i
End Sub
Dim selectedSheets As Sheets
Dim masterSheet As Worksheet
Private Sub Worksheet_Change(ByVal Target As Range)
'Check if the change occured in one of the selected sheets'
If Not Intersect(Target.Worksheet, selectedSheets) Is Nothing Then
'Call the consolidation function when changes are detected'
ConsolidateAndRemoveBlanks
End If
End Sub
Sub ConsolidateAndRemoveBlanks()
Dim ws As Worksheet
Dim lastRow As Long, i As Long, j As Long
'Check if the master sheet exists, if not, create it'
On Error Resume Next
Set masterSheet = Worksheets("MasterSheet")
On Error GoTo 0
If masterSheet Is Nothing Then
Set masterSheet = Sheets.Add(After:=Sheets(Sheets.Count))
masterSheet.Name = "MasterSheet"
Else
'Clear existing data on the master sheet'
masterSheet.Cells.Clear
End If
'Loop through selected sheets and consolidate data'
For Each ws In ActiveWindow.selectedSheets
'Copy data to master sheet'
ws.UsedRange.Copy masterSheet.Cells(masterSheet.Rows.Count, 1).End(x1Up).Offset(1, 0)
Next ws
'Remove blank rows from the master sheet'
lastRow = masterSheet.Cells(masterSheet.Rows.Count, 1).End(x1Up).Row
For i = lastRow To 1 Step -1
For j = 1 To masterSheet.Columns.Count
If IsEmpty(masterSheet.Cells(i, j)) Then
masterSheet.Rows(i).Delete
Exit For
End If
Next j
Next i
End Sub
