Update master sheet automatically when changes are made to split sheet VBA

helplessnoobatexcel

New Member
Joined
Dec 15, 2023
Messages
45
Office Version
  1. 365
Platform
  1. Windows
Hi All,
I have this code in which it would split the master sheet automatically based on values in a specific column and the worksheets would be named based on values in the column. Changes would be shown on the split sheet after changes has been made to the master sheet, but I would also like it to be vice versa. Meaning, that if changes are made to specific cells in a split worksheet, it would automatically update the master sheet to display the new values. Any idea on how I can do this? Thanks!
Option Explicit

Public Sub SplitTableToSheets()

Dim full_data_listobject As ListObject
Dim selected_range As Range
Dim selected_data As Variant
Dim selected_data_header As String
Dim items_sheet As Worksheet
Dim items_range As Range
Dim item As String
Dim i As Integer
Dim new_item_sheet As Worksheet

DeleteSheetWithoutWarning "items"

Set full_data_listobject = ThisWorkbook.Worksheets("full data").ListObjects(1)


' get the range currently selected
Set selected_range = Selection
selected_data_header = Selection.Offset(-1, 0).Cells(1, 1)

Set items_sheet = ThisWorkbook.Worksheets.Add

items_sheet.Name = "items"

selected_range.Copy

items_sheet.Range("A1").PasteSpecial xlValues

Set items_range = items_sheet.Range("A1").CurrentRegion

items_range.RemoveDuplicates 1, xlNo

'loop through each item and create a new sheet with the filtered data
For i = 1 To items_range.Rows.Count
If items_range.Cells(i, 1) <> "" Then
item = items_range.Cells(i, 1)

DeleteSheetWithoutWarning item

full_data_listobject.Range.Copy

Set new_item_sheet = ThisWorkbook.Worksheets.Add

With new_item_sheet
.Name = item
.Range("A1").Formula2 = "=" & full_data_listobject.Name & "[#Headers]"
.Range("A2").Formula2 = "=FILTER(" & full_data_listobject.Name & _
"," & full_data_listobject.Name & "[" & selected_data_header & "] = """ & item & """)"

''optionally uncomment these two lines to paste the results as values in the destination sheet
'.Range("A1").CurrentRegion.Copy
'.Range("A1").PasteSpecial xlPasteValues
End With

Else
Exit For
End If
Next i


End Sub


Private Sub DeleteSheetWithoutWarning(sheet_name As String)

Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets(sheet_name).Delete
On Error GoTo 0
Application.DisplayAlerts = True

End Sub
this is the current code i've gotten online
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Forum statistics

Threads
1,215,069
Messages
6,122,952
Members
449,095
Latest member
nmaske

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top