I have a question. I am trying to DELETE checkboxes from a activesheet and when one cell meets a criteria it should delete the checkboxes and move it automatically to another sheet.. I have the code to automatically move my rows to another sheet, however it is not DEleting the checkboxes from the activesheet? i tried the code "Dim = ckb.delete" above however once it removes the checkboxes from the active sheet it will not let me copy and paste another set with uncheck boxes for my new data.. below is my code for moving the rows over to another sheet when column Q = "Y". I need to remove/delete the checkboxes as well listed in columns "I through P" when the row meets my criteria. I am missing something and I cant figure out how to delete my checkboxes within the range when my cell meets the criteria.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Active").UsedRange.Rows.Count
J = Worksheets("Closed").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Closed").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Active").Range("q1:q" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "y" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Closed").Range("A" & J + 1)
xRg(K).EntireRow.Delete
If CStr(xRg(K).Value) = "y" Then
K = K - 1
End If
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub
Aucun commentaire:
Enregistrer un commentaire