Cut/copy and Paste data to another sheet if only one conidtion is met

roqalexander

New Member
Joined
Jun 18, 2013
Messages
3
Hello,


I am new to this so please excuse my ignroance... this is my first post...

I just have a question I want to create a macro that cuts data from a certain range in one sheet and then pastes that data into another sheet if one condition is met.

So I have attached this spreadsheet as an example..

in the first sheet= (Open administrative items) where it says "Partcipant Items" There is information that says
Peter Dunkin blah blah blah.. the range A24:G39

So the Macro does this: the first sheet- ONLY the range A24:G39 I want certain rows of data CUT ONLY if the status says COMPLETED and Pasted into the second sheet but when it is pasted it also inserts new row on the top (A2) so it doesnt disrupt the data below.

Screen Shot 2020-10-16 at 5.18.37 PM.PNG
Screen Shot 2020-10-16 at 5.18.54 PM.PNG


can this be done?

Thanks for the help in advance!!
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

cooper645

Well-known Member
Joined
Nov 16, 2013
Messages
622
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
This should do as you require:

VBA Code:
Option Explicit

Sub MoveComplete()

Dim c As Range, Rng As Range
Set Rng = Sheets("Open Administerative Items").Range("G24:G39")

    For Each c In Rng
        If c.Value = "Completed" Then
        Sheets("Participant Items Completed").Range("2:2").Insert xlDown
            c.EntireRow.Cut Sheets("Participant Items Completed").Range("2:2")
        End If
    Next c

End Sub
 

roqalexander

New Member
Joined
Jun 18, 2013
Messages
3
This should do as you require:

VBA Code:
Option Explicit

Sub MoveComplete()

Dim c As Range, Rng As Range
Set Rng = Sheets("Open Administerative Items").Range("G24:G39")

    For Each c In Rng
        If c.Value = "Completed" Then
        Sheets("Participant Items Completed").Range("2:2").Insert xlDown
            c.EntireRow.Cut Sheets("Participant Items Completed").Range("2:2")
        End If
    Next c

End Sub


amazing thank you so much wow great response time and everything cannot thank you enough
 

Watch MrExcel Video

Forum statistics

Threads
1,127,339
Messages
5,624,101
Members
416,011
Latest member
chengkoonwing

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