VBA to copy row data into first empty row based on cell value

[Jebboh]

Hey,

I am running a project list from which I need to copy selected range from single row into another sheet first available blank row depending on project status. Essentially, I need to have a VBA to do the following:

If "ID"-sheet column H = "active", then copy range from that specific row into "Active projects"-sheet first blank row
Else if "ID"-sheet column H = "planned", then copy range from that specific row into "Planned projects" -sheet first blank row

Bonuspoints should the VBA be also able to delete the specific row from "planned projects"-sheet when status is changed into "active"

All help much appreciated!

Best,
Jebboh


EDIT: Might make more sense to provide bonuspoints if the VBA would be able to cut-paste the whole row from "planned" to "active" when status changed on the "ID" cheet.

 

[My Aswer Is This]

You said:
I need to copy selected range

You said:
If "ID"-sheet column H = "active", then copy range from that specific row
I assume you mean sheet named "ID"
You said copy range.
Do you mean entire row?

 

[Jebboh]

You said:
I need to copy selected range

You said:
If "ID"-sheet column H = "active", then copy range from that specific row
I assume you mean sheet named "ID"
You said copy range.
Do you mean entire row?

Hey,

I might actually end up needing both. I've not done any VBA prior although I am somewhat proficient in reading code so I am ultimately trying to learn couple of different ways of doing this via VBA. So in case you can provide a snippet of code for both copying any full row, copying either a range (i.e. from A-G) or selected few cells (i.e A, C and D) from the row based on the active/planned status in the column H, it would be much appreciated.

Thanks in advance!

Best,
Jebboh

 

[My Aswer Is This]

Try this:

Sub Copy_Range()
'Modified  11/8/2021  6:40:07 PM  EST
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Sheets("Active projects").Cells(Rows.Count, "A").End(xlUp).Row + 1

If Sheets("ID").Range("H1").Value = "active" Then
    Sheets("ID").Rows(1).Copy Sheets("Active projects").Rows(Lastrow)
End If
Application.ScreenUpdating = True
End Sub

 

[vcoolio]

Hello Jebboh,

Another option for you:-

Option Explicit

Sub Test()

        Dim wsID As Worksheet, wsD As Worksheet
        Dim ar As Variant, i As Integer
        Set wsID = Sheets("ID")
        ar = [{"Active projects","Planned projects";"Active","Planned"}]

Application.ScreenUpdating = False
        
        For i = 1 To UBound(ar, 2)
                        Set wsD = Sheets(ar(1, i))
                        wsD.UsedRange.Offset(1).Clear
                With wsID.[A1].CurrentRegion
                        .AutoFilter 8, ar(2, i)
                        .Offset(1).EntireRow.Copy wsD.Range("A" & Rows.Count).End(3)(2)
                        .AutoFilter
                        wsD.Columns.AutoFit
                End With
         Next i
        
Application.ScreenUpdating = True
MsgBox "All done!", vbExclamation

End Sub

Might make more sense to provide bonus points if the VBA would be able to cut-paste the whole row from "planned" to "active" when status changed on the "ID" sheet.

Change the status in the ID sheet then run the code again. It should do the task for you.

Please test the code in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.