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

[roqalexander]

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.

can this be done?

Thanks for the help in advance!!

 

[cooper645]

This should do as you require:

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]

This should do as you require:

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

 

[cooper645]

no problem