Inserting row into another sheet VBA

February 12, 2012 at 16:27:33
Specs: Windows 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??


See More: Inserting row into another sheet VBA

Report •


#1
February 12, 2012 at 19:59:09
First, a posting tip. Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum. If you follow those instructions, it will be much easier for us to read your code.

Second, I'm curious as to why you used the SelectionChange event to trigger this code. It's going to run each and every time a cell, any cell, is selected in the worksheet in which it is stored.

Is that really what you want to happen?

Third, look carefully at your code and figure out what the "Selection" is at the time that the line is executed.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 20, 2012 at 07:31:39
Sorry for the formatting, this was my first post. After looking over my code, I don't see what I have selected. I don't have code that specifies what is being selected (as far as I can see).

Report •

#3
February 20, 2012 at 12:33:36
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. ↓ ↓ ↓


Report •

Related Solutions

#4
February 20, 2012 at 15:52:02
I asked you why you used the SelectionChange event to trigger this code.

As I said, it's going to run each and every time you select a cell or cells anywhere in the sheet in which the code resides.

Since it is triggered when you select a cell, that cell is going to be your Selection and that is where the Insert is going to occur.

Even though your code doesn't select anything, there is still a "Selection" for the code to work on: whatever cell was selected to trigger the code.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
February 20, 2012 at 15:59:13
Razor2.3,

While that site is pretty cool, and I appreciate the link, I look at this way:

If a member comes here looking for help, I don't feel that those of us that are going to provide that help should have to do extra work just to be able to read the code easier.

I'd rather the OP do the work and repost the code with the indents. If they are asked to do it once, odds are they'll do it right the next time.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question