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

Jebboh

New Member
Joined
Nov 8, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,233
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Nov 8, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
18,233
Office Version
  1. 2013
Platform
  1. Windows
Try this:
VBA Code:
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

Well-known Member
Joined
Jun 29, 2014
Messages
1,226
Office Version
  1. 365
Platform
  1. Windows
Hello Jebboh,

Another option for you:-

VBA Code:
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,488
Messages
5,770,380
Members
425,613
Latest member
martinijr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top