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

roqalexander

New Member
Joined
Jun 18, 2013
Messages
8
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!!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,249
Messages
6,171,031
Members
452,374
Latest member
keccles

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
Back
Top