stuckyetagain
New Member
- Joined
- May 31, 2015
- Messages
- 3
I have 2 macros that deploy an advancedfilter called by the worksheet_change event dependant on a data validation list. This works fine to copy the data from the master list to the individual tabs and sort information for easy viewing. When the data is deleted however the tabs aren't updated. Can anybody help teach me how to make the tabs refresh when the data is deleted. I am attaching samples of code to show what I have done so far.
The if isempty formula doesn't seem to be achieving the desired outcome of deleting the filtered data to the tabs when the original data is deleted.
Any suggestions would be greatly appreciated.
Code:
Private Sub WorkSheet_Change(ByVal Target As Range)
Select Case Target
Case "Warehouse"
Worksheets("Floor Stock").Activate
Call GetFloorStock
Worksheets("Warehouse").Activate
Call GetWarehouse
Case "Floor Stock"
Worksheets("Warehouse").Activate
Call GetWarehouse
Worksheets("Floor Stock").Activate
Call GetFloorStock
Case Else:
End Select
Code:
Sub GetFloorStock()
'
' GetFloorStock Macro
'
'
ActiveSheet.Cells.Clear
Dim NextRow As Long
With Sheets("Floor Stock")
If Not IsEmpty(ThisWorkbook.Sheets("Phase 1").Range("a2").Value) Then
NextRow = .Range("A65536").End(xlUp).Row
Sheets("Phase 1").Columns("A:L").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet4").Range("c1:c2"), CopyToRange:=.Range("A" & NextRow), _
unique:=True
End If
If IsEmpty(ThisWorkbook.Sheets("Phase 1").Range("a2").Value) Then
ActiveWorkbook.Worksheets("Floor Stock").Select
NextRow = .Range("A65536").End(xlUp).Row + 1
.Range("A" & NextRow).EntireRow.Delete
End If
The if isempty formula doesn't seem to be achieving the desired outcome of deleting the filtered data to the tabs when the original data is deleted.
Any suggestions would be greatly appreciated.