I know this similar request has been made before on other posts but I can't find a solution to this particular issue.
I am using the below code to copy row data from one sheet to another based on criteria in a column. Easy enough. I have an "update" button to call the macro and it works well, save two features.
If there is missing info in a row I wanted a box to pop up "Please fill out row" or something like that. Right now an error box with "400" displays.
Second, if I delete the first row on the "Master" sheet and run the macro, it won't reflect that deletion on the sub sheet. This only happens for the first row because I don't know how to program a "clear contents" that deals with the change/update. I have also attached a sheet.
Bonus -- how could I make this code refer to table data?
Sub ToSet()
Dim rT As Range 'source data
Dim rD As Range 'data minus headers
Dim wS As Worksheet 'source sheet
Dim wT As Worksheet 'target sheet
Dim wsRow As Long
Dim b As Boolean
Set wS = Worksheets("MASTER")
With wS
.AutoFilterMode = False
Set rT = .Range("A1", .Cells(1, Columns.Count).End(xlToLeft)) 'data width
Set rT = rT.Resize(.Cells(.Rows.Count, 3).End(xlUp).Row) 'data height including header
Set rD = rT.Offset(1).Resize(rT.Rows.Count - 1) 'data height wo header
End With
For Each wT In Worksheets
rT.AutoFilter Field:=7, Criteria1:=wT.Name
On Error Resume Next
b = rD.SpecialCells(xlCellTypeVisible).Count > 1 'check if data for this sheet
On Error GoTo 0
If b Then 'data exists, continue
wT.Range("A2", wT.Cells.SpecialCells(xlLastCell)).Clear 'clear everything below header row
' This next line may not be necessary if new data always placed at row 2
wsRow = wT.Cells(Rows.Count, 2).End(xlUp).Row + 1 'find 1st empty row
rD.SpecialCells(xlCellTypeVisible).Copy wT.Range("A" & wsRow) 'copy over data
End If
Next wT
wS.AutoFilterMode = False
Application.CutCopyMode = False
End Sub
Bookmarks