Automatically move rows into a table on another sheet

January 10, 2019 at 12:27:26
Specs: Windows 10
I am using the macro code displayed below to automatically move rows of information from one sheet to another. Sheet 1 is named Current and Sheet 2 is named Completed. The information is sheet one is displayed as a table. I would like the information that transfers over the the Completed sheet to be in a table form as well. The problem is that when the information transfers over, it inserts itself into the first line outside of the table. Which part of this code do I alter to get it so the information is transferred inside the table on the completed sheet??

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
If Target = "Yes" Then
Application.EnableEvents = False
nxtRow = Sheets("Completed").Range("C" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy _
Destination:=Sheets("Completed").Range("A" & nxtRow)
Target.EntireRow.Delete
End If
End If
Application.EnableEvents = True
End Sub

Any help is greatly appreciated! Thank you!!

I apologize for not being able to figure out how to display the code correctly on this post.

message edited by cq0925


See More: Automatically move rows into a table on another sheet

Reply ↓  Report •

#1
January 10, 2019 at 13:10:26
First, a posting tip:

Please click on the How-To link at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then edit/repost your code so that it looks similar to the example found via that link.

As for your question, the code is written such the rows are pasted into the next empty row based on the data in Column C. This is the instruction that determines that Row number:

nxtRow = Sheets("Completed").Range("C" & Rows.Count).End(xlUp).Row + 1

Once the "nxtRow" variable has been calculated, this instruction uses that value to do the copy/paste:

Target.EntireRow.Copy _
Destination:=Sheets("Completed").Range("A" & nxtRow)

As far as pasting the data "inside the table", I have a question:

Is the range defined as an actual table within Excel or is just some cells formatted so that it looks like a table to the viewer?

How each of those "tables" is dealt with is very different. The more details that you provide, the easier it will be for us to help you.

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


Reply ↓  Report •

#2
January 10, 2019 at 13:16:02
The "inside the table" that I am referring to is an actual table within Excel.

Thank you for the quick reply!


Reply ↓  Report •

#3
January 10, 2019 at 17:50:12
Try this, once you determine the name of the table you are copying to. I used Table1 in the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 3 Then
    If Target = "Yes" Then
    
'Add Row to Table1 and determine Row number
       Set Tbl = Sheets("Completed").ListObjects("Table1")
         Tbl.ListRows.Add
         nxtTblRow = Tbl.ListColumns(3).Range.Rows.Count
          
'Copy/Paste
        Target.EntireRow.Copy _
            Destination:=Tbl.Range(nxtTblRow, 1)
              
'Delete Target Row
        Application.EnableEvents = False
          Target.EntireRow.Delete
        Application.EnableEvents = True
    
    End If
  End If
End Sub

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


Reply ↓  Report •

Related Solutions

#4
January 23, 2019 at 12:41:07
Thank you so much for your help, DerbyDad03! I was able to get the code that you gave me to work with pasting the data INSIDE the table instead.

I do have one more question to add to this. The table that I am wanting the information to transfer to (which is Table 13)...if I have the table made up with a few blank rows already made, when the row transfers over it inserts itself one row below the rows that are already created (even though those rows are blank). It IS still within the table, just on the bottom. Is there a way to get them to paste to the top of the (already created) blank rows, or do I just need to have my table start with only 1 row? Not sure if that makes sense...


Reply ↓  Report •

Ask Question