VBA Code to Move Row Based Upon Condition - Same Worksheet

[savv37]

Hello,
I need some help developing a VBA code to move a row of cells based upon a cell which contains a dropdown list. I'm ok with the basic formulas however when it comes to VBA I'm clueless since I haven't used VBA in a very long time, so any help would be appreciated.

Basically my list is similar to Task List which lists projects, due dates & completion status, fairly basic stuff.

Currently I have my status' setup in a dropdown list as, without the double hyphen
--Completed
--Awarded
--Not Started
--In Progress
--Lost
--Not Bidding

I currently have VBA Code to move the "Lost", "Not Bidding" & "Awarded" to three similarly named Worksheets. What I am attempting to do now is break down my Project List into "Completed", "Not Started" & "In Progress" on the same worksheet versus breaking them out into different worksheets.

I've searched the web & various forums and all I can seem to locate is moving a row to another workbook or worksheet.

Thanks in advance for the help....

Scott

 

[Joe4]

I think the easier thing to do would be to sort all the records by status, then insert a bunch of rows at each change in status and add in whatever headers/totals rows that you want.
That is probably how I would approach it.

If that sounds like it might work, you can use the Macro Recorder to get some of the code that you need (such as the sorting, inserting of rows, adding of headers, etc).
Some of the code will probably have to be written (such as where to identify the changes in status), but we can help with that if you provide the needed details for that (i.e. what column this Status will be in, etc).

 

[My Aswer Is This]

Try this:

Sub Copy_Row_Down()
'Modified 3-20-18 11:05 PM EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim x As Long
x = 0
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        If Cells(i, 1).Value = "Not Started" Then: x = x + 1: Rows(i).Copy Rows(Lastrow + x)
    Next
    For i = 1 To Lastrow
        If Cells(i, 1).Value = "In Progress" Then: x = x + 1: Rows(i).Copy Rows(Lastrow + x)
    Next
    For i = 1 To Lastrow
        If Cells(i, 1).Value = "Completed" Then: x = x + 1: Rows(i).Copy Rows(Lastrow + x)
    Next
Rows("1:" & Lastrow).Delete
Application.ScreenUpdating = True
End Sub

 

[savv37]

If that sounds like it might work, you can use the Macro Recorder to get some of the code that you need (such as the sorting, inserting of rows, adding of headers, etc).
Some of the code will probably have to be written (such as where to identify the changes in status), but we can help with that if you provide the needed details for that (i.e. what column this Status will be in, etc).

Thanks Joe...
I can tell you that the Status Column currently always resides in Column E, however this could move if you needed it to reposition.

As for the Changes in Status, this is difficult to know as it depends upon workload, however if we count up from the bottom, cell E1048576, and search for the first occurrence of the word "Completed" we could insert a heading that consists of column headings and the word "Completed" entered into cell A-ZZ with a colored background spanning from A-ZZ to T-ZZ plus 3 blank rows starting at say A-ZZ - 3. This same procedure could be used for the "In Progress" & "Not Started" work with the exclusion that this data would be moved in-between the "Incoming Bids" & "Completed". So it would look something like...

Not that it matters but it might help in visualization, all of this data will reside on a 50 or 60-inch monitor turned to portrait mode so it can be seen & read by everyone in the office.


ROW 1: GENERAL HEADING [COMPANY NAME, GENERAL SHEET TITLE, ANY REQUIRED BUTTONS, ETC.]
ROW 2: COLUMN HEADINGS [COL A THRU T, THESE ARE ALWAYS THE SAME]
ROW 3: INCOMING BIDS ------------------------------------------------------------------------------------------------------- (THIS CAN RUN TO COLUMN T) [THIS IS WHERE A DATA ENTRY PERSON WOULD INPUT POTENTIAL LEADS, UNTIL THEY ARE REVIEWED & ASSIGNED]
ROW 4-20: BLANK ROWS FOR INCOMING BID DATA INPUT [THIS CAN EITHER BE EXPANDED OR CONTRACTED BY EITHER INSERTING ROWS OR DELETING ROWS]
ROW 21: EMPTY ROW [ROW NUMBERS WOULD VARY DEPENDING UPON THE NUMBER OF ROWS BETWEEN ROW 3 & LAST RECORD OF THIS SECTION]
ROW 22: EMPTY ROW[ROW NUMBERS WOULD VARY DEPENDING UPON THE NUMBER OF ROWS BETWEEN ROW 3 & LAST RECORD OF THIS SECTION]

CARRIAGE RETURNS ENTERED FOR CLARITY ONLY

ROW 23: COLORED BAR SPANNING COLUMNS "A" THRU "T"

CARRIAGE RETURNS ENTERED FOR CLARITY ONLY

A-XX - 3 [EMPTY ROW]
A-XX - 2 [EMPTY ROW]
A-XX - 1 [EMPTY ROW]
NOT STARTED ------------------------------------------------------------------------------------------------------- (THIS CAN RUN TO COLUMN T)
COLUMN HEADINGS [COL A THRU T]
MOVED ROW DATA 1
MOVED ROW DATA 2
MOVED ROW DATA 3
MOVED ROW DATA 4
an so on... until all data has been moved (if possible it would be great if this data was sorted by date & project name.)

CARRIAGE RETURNS ENTERED FOR CLARITY ONLY

A-YY - 3 [EMPTY ROW]
A-YY - 2 [EMPTY ROW]
A-YY - 1 [EMPTY ROW]
IN PROGRESS ------------------------------------------------------------------------------------------------------- (THIS CAN RUN TO COLUMN T)
COLUMN HEADINGS [COL A THRU T]
MOVED ROW DATA 1
MOVED ROW DATA 2
MOVED ROW DATA 3
MOVED ROW DATA 4
an so on... until all data has been moved (if possible it would be great if this data was sorted by date & project name.)

CARRIAGE RETURNS ENTERED FOR CLARITY ONLY

A-ZZ - 3 [EMPTY ROW]
A-ZZ - 2 [EMPTY ROW]
A-ZZ - 1 [EMPTY ROW]
COMPLETED ------------------------------------------------------------------------------------------------------- (THIS CAN RUN TO COLUMN T)
COLUMN HEADINGS [COL A THRU T]
MOVED ROW DATA 1
MOVED ROW DATA 2
MOVED ROW DATA 3
MOVED ROW DATA 4
an so on... until all data has been moved (if possible it would be great if this data was sorted by date & project name)

 

[Joe4]

As for the Changes in Status, this is difficult to know as it depends upon workload, however if we count up from the bottom, cell E1048576, and search for the first occurrence of the word "Completed" we could insert a heading that consists of column headings and the word "Completed" entered into cell A-ZZ with a colored background spanning from A-ZZ to T-ZZ plus 3 blank rows starting at say A-ZZ - 3.

See if this code snippet helps get you started:

    Dim lRow As Long
    Dim r As Long
    
'   Find last row in column E with data
    lRow = Cells(Rows.Count, "E").End(xlUp).Row
    
'   Loop up in column E until you find the work "Completed"
    For r = lRow To 1 Step -1
        If Cells(r, "E") = "Completed" Then
            'do headings and formatting here
            Exit For
        End If
    Next r

Note that you can also get a lot of the VBA code that you need for things like formatting, etc with the Macro Recorder. Just turn on the Macro Recorder and record yourself manually performing the actions you want the VBA code for, and you will get a lot of the code you need.

 

[My Aswer Is This]

In post number 5 it shows your status column is column A but then in your last post you said:

Thanks Joe...
I can tell you that the Status Column currently always resides in Column E,​

My script put's your status rows in order assuming column A is the status column
Now my script does not insert a header row before each change in status. But I could insert that.
What do you want this header row to look like?