Home/ Questions/Q 14613
Next
Answered
computing
  • 7
Asked: 2021-12-15T11:53:38+00:00 In:

Inserting Row Into Another Sheet VBA

  • 7

I am trying to insert a row that meets a cell criteria into another sheet. When it copies over to the second sheet, it just replaces the first row instead of shifting the cells. Here is my code below. I have the “Selection.Insert Shift:=xlDown” line of code which I thought would shift the cells down before inserting, but it’s not working.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

‘ The code below copies any row with column “B” containing the “Completed”
‘ to the “Completed Tasks” sheet.

Set t = Sheets(“Tasks”)
Set c = Sheets(“Completed Tasks”)
Dim d
Dim j
d = 1
j = 2

Do Until IsEmpty(t.Range(“B” & j))

If t.Range(“B” & j) = “Completed” Then
d = d + 1
c.Rows(d).Value = t.Rows(j).Value
Selection.Insert Shift:=xlDown

End If
j = j + 1
Loop

End Sub

Any ideas??

Share

    1 Answer

    1. Best Answer
      Entirely off topic, but I find vbindent.com invaluable when reading vb* here.

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
' The code below copies any row with column "B" containing the "Completed"
' to the "Completed Tasks" sheet.
  
  Set t = Sheets("Tasks")
  Set c = Sheets("Completed Tasks")
  Dim d
  Dim j
  d = 1
  j = 2
  
  Do Until IsEmpty(t.Range("B" & j))
    
    If t.Range("B" & j) = "Completed" Then
      d = d + 1
      c.Rows(d).Value = t.Rows(j).Value
      Selection.Insert Shift:=xlDown
      
    End If
    j = j + 1
  Loop
  
End Sub

      How To Ask Questions The Smart Way

      EDIT: ↓ ↓ ↓ You’re such an optimist, DerbyDad03. ↓ ↓ ↓

      • 0