Solved 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

Report •

✔ Best Answer
February 4, 2019 at 12:04:30
OK, to keep the code as simple as possible, set up your table so that it has a single blank row below the data. If there is no data yet, just place a blank row under the header row.

If you have a table that already has more than one blank row under your data, delete all blank rows except for one.

When Yes is entered in Column C of the Current sheet, a new blank row will be added to the table and the Target row will be pasted into next to last row. You'll should always end up with one blank row at the bottom of the table unless you manually fill that blank row. In order for the code to work correctly, a blank row must exist as the last row of the table. If that is not going to be the case, then you'll need to provide some more detail.

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Column = 3 Then
    If Target = "Yes" Then
        Set Tbl = Sheets("Completed").ListObjects("Table1")
        
'Add a blank row, count rows based on Column 3
       Tbl.ListRows.Add
         lastTblRow = Tbl.Range.Rows.Count
         
'Copy/Paste Target in next-to-last row
        Target.EntireRow.Copy _
            Destination:=Tbl.Range(lastTblRow - 1, 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



#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


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!


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


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...


Report •

#5
January 24, 2019 at 10:31:59
I need some further details.

You said "I have the table made up with a few blank rows already made"

Then you asked: "Is there a way to get them to paste to the top of the (already created) blank rows"

Yes, that can be done but I'm not sure if you are trying to fill up those empty rows and then start adding new rows or if you always want to have "a few blank rows" at the bottom of the table.

In other words, if you have 4 rows of data and 3 blanks rows at the bottom, should the paste result in 5 rows of data and 3 blank rows or 5 rows of data and 2 blank rows, then 6 and 1, 7 and none, 8 and none, etc.

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


Report •

#6
January 30, 2019 at 14:19:38
Yes my description was very confusing, sorry about that!

Example: If I had 2 rows of data and one blank row below it, I would like the pasted data to be entered below the 2 data rows and continue to have 1 blank row below that. Right now I have a table with 2 BLANK rows (Row 3 and 4) and when the pasted data copies over it is inserted into row 5 rather than into the blank row. I would like it to move into row 3 and so on.


Report •

#7
January 31, 2019 at 06:15:58
Your description is still confusing.

You said: "If I had 2 rows of data and one blank row below it, I would like the pasted data to be entered below the 2 data rows and continue to have 1 blank row below that"

Then you said: "Right now I have a table with 2 BLANK rows"

Why are you giving me an "IF" one blank row scenario when you currently have two blank rows? Why not just delete the 2nd blank row and tell me that you always want one blank row at the bottom of the table?

If you expecting the code to fill in the first of your 2 blank rows now and then only leave one blank row from now on, then the code will have to check for that "extra" blank row every time but only deal with it once - the first time. That is not very efficient. Again, why not just delete the 2nd blank row manually - right now - so that the code can be simpler: Add a row, paste the data one row below the existing data. You'll always have one blank row at the bottom of the table.


message edited by DerbyDad03


Report •

#8
January 31, 2019 at 12:55:09
I'm so sorry. I am having a hard time trying to explain what is happening and having it make any sense.

Let me start over. This is what I am currently working with.

On my "Completed" sheet, which is the one I would like the data transferred to, there is an actual excel table (Table 13) with 1 line that is blank (no data currently entered in that line).
When the information is transferred over, it is entered BELOW the blank line, not above. I don't mind at all having blank rows below my information, I just can't figure out how to get it so there isn't blank rows above the information that is being transferred over.
If i start the table with no rows, the information is transferrerd OUTSIDE of the table. If I start the table with 1 blank row, the information is entered 1 row below that blank row and still within the table.

Long, and very confusing, story short, I just want my transferred rows to start at first row (row 3) of the table (below the headers) and work it's way down.


Report •

#9
February 4, 2019 at 12:04:30
✔ Best Answer
OK, to keep the code as simple as possible, set up your table so that it has a single blank row below the data. If there is no data yet, just place a blank row under the header row.

If you have a table that already has more than one blank row under your data, delete all blank rows except for one.

When Yes is entered in Column C of the Current sheet, a new blank row will be added to the table and the Target row will be pasted into next to last row. You'll should always end up with one blank row at the bottom of the table unless you manually fill that blank row. In order for the code to work correctly, a blank row must exist as the last row of the table. If that is not going to be the case, then you'll need to provide some more detail.

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Column = 3 Then
    If Target = "Yes" Then
        Set Tbl = Sheets("Completed").ListObjects("Table1")
        
'Add a blank row, count rows based on Column 3
       Tbl.ListRows.Add
         lastTblRow = Tbl.Range.Rows.Count
         
'Copy/Paste Target in next-to-last row
        Target.EntireRow.Copy _
            Destination:=Tbl.Range(lastTblRow - 1, 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


Report •

#10
February 6, 2019 at 11:58:59
This works perfect!!! Thank you so much for being so patient with me and helping me through this!! Very much appreciated :-)

Report •

#11
February 8, 2019 at 13:36:26
DerbyDad,

I am trying to take this code to another level and I have added a code on there to take rows of data and COPY them to a separate spreadsheet based on what is selected in the drop down menu in Column 4. The code that I have attached to what you helped me do previously will copy items to a spreadsheet in a seperate tab named 'KK To Do' when "Move to KK" is selected from the drop down menu. I would like to do the same for 3 others by adding to the code which would be:

Move to "JM To Do" when "Move to JM" is selected
Move to "CQ To Do" when "Move to CQ" is selected
Move to "BM To Do" when "Move to BM" is selected

Here is my current code

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Column = 6 Then
    If Target = "Yes" Then
        Set Tbl = Sheets("Completed Tasks").ListObjects("Table1")
        
'Add a blank row, count rows based on Column 6
       Tbl.ListRows.Add
         lastTblRow = Tbl.Range.Rows.Count
         
'Copy/Paste Target in next-to-last row
        Target.EntireRow.Copy _
            Destination:=Tbl.Range(lastTblRow - 1, 1)

'Delete Target Row
        Application.EnableEvents = False
          Target.EntireRow.Delete
        Application.EnableEvents = True
   End If

     ElseIf Target.Column = 4 Then
          If Target = "Move to KK" Then
       
     Application.EnableEvents = False
           nxtRow = Sheets("KK To Do").Range("D" & Rows.Count).End(xlUp).Row + 1
     
     Target.EntireRow.Copy _
         Destination:=Sheets("KK To Do").Range("A" & nxtRow)
End If
     Application.EnableEvents = True

End If
End Sub

The code works great for the KK To Do. I tried to copy and paste that section of code and change the KK to JS, but when I try to type anything on the spreadsheet it gives me a "Compile Error, Else without If" on this line...

ElseIf Target.Column = 4 Then

Any suggestions?? I can provide additional information if needed!

One thing I wanted to note is that in my previous posts I was moving the rows into an excel table on a separate sheet. For this copy code, the rows are not actually going into an excel table, just copying them to a separate sheet.

message edited by cq0925


Report •

Ask Question