Hi all, recently I've been working on a new project that uses a macro to move rows of information across sheets, from sheets we call Triage to Open Tasks to Closed Tasks.



We do this to stimulate a work flow and actively management what tasks are being done etc.

I've ran into a bit of a problem,
Triage is a table, Open Tasks is a table and Closed tasks is range.

The issue is inbetween Triage and Open tasks, when I use the macro to move a row of information from triage to Open, it adds to the worksheet as part of a range and doesn't go into the table that I have in the Open tasks - this is a problem because when we go to Open tasks and start filtering information, it only filters the table stuff and not the range stuff, we really need the information to move into the table so it is considered in the filtering, we considered converting the table to a range, however, that defeats our automatic formulas that we get with having the information in a table.

Is there a way to assign the macro to move information into a table as opposed to the worksheet?

Any help is appreciated, thanks in advance.

The macro I use to move information is this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim nxtRow As Long

' Limit to a single cell change in columns 21
If Target.Count > 1 Then Exit Sub
If Target.Column < 21 Or Target.Column > 22 Then Exit Sub

' If column 21
If Target.Column = 21 And UCase(Target.Value) = "YES" Then
nxtRow = Sheets("Open tasks").Range("U" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy _
Destination:=Sheets("Open tasks").Range("A" & nxtRow)
Application.EnableEvents = False
Target.EntireRow.Delete
Application.EnableEvents = True
Exit Sub
End If
End Sub

Yes... add it as a listrow within the table or as an addition to the bottom of the table.
See the 'Inserting rows and columns' part on this site.
The VBA Guide To ListObject Excel Tables is another site dealing with tables.

Thank you NoSparks, I will read into the links that you've given and hopefully I will be able to edit the code for the purpose.

Hi NoSparks and others that may be able to help me,

I've been struggling to edit the code to target the table in a different worksheet as opposed to just targeting the range. I read the guides supplied and edited as suggested but I'm assuming that there needs to be extra lines that I have no idea about.

I've supplied a sample of what I am trying to achieve in the attached file.

In the workbook, I have 3 sheets, Triage, Open Tasks, Closed Tasks. It is used to stimulate a workflow, where Triage is where information is keyed in, then travels to Open tasks and Closed tasks when open and closed.

The VBA code cuts and pastes rows automatically, however, it is targeting a range and this is a problem as the information in the open Tasks sheet needs to be in table so that formula and conditional formatting assigned to the table will be duplicated as new entries are moved to Open.

Help would be appreciated in helping to target the Table rather than a range.

Thanks in Advance,

Attached Files

Example workflow tracker.xlsm (19.7 KB, 13 views)

The code you have is adding the data as the next record in the table, which is what you want.

The issue you have, or what you are seeing, is due to blank records (rows) in your table.

When you put your cursor on the row number at the left of the sheet and click, it highlight the entire row for deletion.
Deleting the entire row removes data from the table and moves the below data up but the number of table records (rows) remains the same, creating blank records at the bottom of the table.

If you put your cursor half on the row number and half in the first column and click, it will highlight only the table record for deletion.
Deleting now will move the remainder of the table up, decreasing the number of table records.

If you enter some data in to the table on Triage, for example typing in TEST5 in the next row, then type in 'Yes' in the 'Move to Open' column, the row will move into the Open Tasks.

Moving to the next sheet, Open Tasks, the information is there but not in the table, if we select Table2 in this workbook, it highlights the whole of table2 but the information that has just been moved over from the Triage has not moved to the table.

Sorry if i'm not making any sense but I hope it does!

I have to admit I don't understand what's going on with that table on the "Open Tasks" sheet.
It's supposed to behave the same as the table on the "Triage" sheet, but for some reason it won't expand.

Try this for the change event

Perfect, this has solved the problem.

Thanks again for the help NoSparks.