Can you please help me in using the below code to AutoFilter across multiple sheets. I have included a picture. Cells E4, E5, and E6 I would like to be replicated on other sheets as they are changed on the main sheet via a list. Thank you
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E4:E6")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
' Change "E3" to the cell the user completes the year in
Const tlbName = "Table9" 'update to name of table name on relevant sheet
Dim DateCol, MinYear, MaxYear
DateCol = "F21:F" & Range("F" & Rows.Count).End(xlUp).Row 'change to match your date column
MinYear = Year(WorksheetFunction.Min(ActiveSheet.Range(DateCol)))
MaxYear = Year(WorksheetFunction.Max(ActiveSheet.Range(DateCol)))
With ActiveSheet
If Target.Value >= MinYear And Target.Value <= MaxYear Then
.ListObjects(tlbName).Range.Columns(1).AutoFilter Field:=1, Criteria1:="TRUE"
Else
.ListObjects(tlbName).Range.Columns(1).AutoFilter Field:=1, Criteria1:="TRUE"
End If
End With
Broker_List
End If
'
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 20
xTimeColumn = 19
xRow = Target.Row
xCol = Target.Column
If Target.Text <> "" Then
If xCol = xCellColumn Then
Cells(xRow, xTimeColumn) = Now()
Else
'On Error Resume Next
Set xDPRg = Target '.Dependents
For Each xRg In xDPRg
If xRg.Column = xCellColumn Then
Cells(xRg.Row, xTimeColumn) = Now()
End If
Next
End If
End If
End Sub
Sub GetListObjectNames()
Dim ws As Worksheet
Dim lo As ListObject
Dim rng As Range
Set ws = ActiveSheet
For Each lo In ws.ListObjects
Debug.Print lo.Name
MsgBox lo.Name
Next lo
End Sub
Sub Broker_List()
Dim LastRowID1 As Integer
Dim LastRowID2 As Integer
Dim StagingRange
Dim UniqueRange
'clear current broker list
Sheets("Market Overview").Select
Sheets("Market Overview").Range("C7").Select
Sheets("Market Overview").Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Sheets("Market Overview").Range("C7").Select
'get new broker list
Sheets("Inventory Tracker").Select
Sheets("Inventory Tracker").Range("Table9[[#Headers],[Broker]]").Select
Sheets("Inventory Tracker").Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
'manipulate data on reference sheet
Sheets("Reference").Select
Sheets("Reference").Range("BrokerStaging").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
LastRowID1 = ThisWorkbook.Worksheets("Reference").Cells(Rows.Count, 20).End(xlUp).Row
StagingRange = "T2:T" & LastRowID1
ActiveWorkbook.Sheets("Reference").Range(StagingRange).Select
Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Reference").Range("BrokerUnique"), Unique:=True
ActiveWorkbook.Sheets("Reference").Range("BrokerUnique").Select
LastRowID2 = ThisWorkbook.Worksheets("Reference").Cells(Rows.Count, 21).End(xlUp).Row
UniqueRange = "U3:U" & LastRowID2
ActiveWorkbook.Sheets("Reference").Range(UniqueRange).Select
' Selection.Sort.SortFields.Add Key:=Range( _
' UniqueRange), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
' xlSortNormal
ActiveWorkbook.Worksheets("Reference").Range(UniqueRange).Copy
'copy new broker list
Sheets("Market Overview").Select
Sheets("Market Overview").Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Sheets("Market Overview").Range("Year_Market").Value = Sheets("Reference").Range("Year_Market").Value
'Sheets("Market Overview").Range("SLMM_Market").Value = Sheets("Reference").Range("SLMM_Market").Value
'Sheets("Market Overview").Range("Rep_Market").Value = Sheets("Reference").Range("Rep_Market").Value
'clear data on reference
ActiveWorkbook.Worksheets("Reference").Range(UniqueRange).ClearContents
ActiveWorkbook.Worksheets("Reference").Range(StagingRange).ClearContents
Sheets("Inventory Tracker").Select
Sheets("Inventory Tracker").Range("Rep_Master").Select
End Sub