Simple Macro to cut and paste based on cell value

[norts55]

Can anyone share a simple macro that will cut and paste an entire row from one worksheet to another based on part of a cell value within one cell in a certain column? Below I kind of a step by step...

Search Column "C" on worksheet "All" for the word "Time".
Select all (entire) rows that have the word "Time" in column "C"
Cut those selected rows and paste to the top of the worksheet "All Time" - (This worksheet will be empty until the paste occurs)<o></o>

 

[bobalouie76]

That works. Thank You!

I have a similar need to the above poster. I tried using the macro that was provided and modifying it to match the conditions in my worksheet but it doesn't seem to work... Here is what I am trying to accomplish.

I have a spreadsheet for work with old tickets on it. The spreadsheet has 2 sheets on it, "open" and "Closed". I have a column, column K where the word Yes will be entered once the ticket has been closed. I would like to have that row cut from the "Open" sheet and pasted to the top of the "Closed" Sheet.

Please feel free to ask any questions if I didn't explain what I need clearly enough, and thank you in advance!

 

[Titian]

I understand that VOG has retired now, pehaps you should post the code you have so far and someone may be along later to help

 

[bobalouie76]

Copied and altered:
Sub atest()
Dim LR As Long, i As Long
With Sheets("Yes")
LR = .Range("K" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With .Range("K" & i)
If .Value Like "*Yes*" Then .EntireRow.Cut Destination:=Sheets("Closed").Range("K" & Rows.Count).End(xlUp).Offset(1, -2)
End With
Next i
End With
End Sub

I don't really know anything about how Macros are written so what I changed may make no sense. When I try to run that I get an error:
Run-time error '9'
Subscript out of range

It points to the following as a problem:
With Sheets("Yes")

Thanks again

 

[Fluff]

Try changing that line to

With Sheets("Open")

 

[bobalouie76]

That got me 1 step closer. Now I get the following error:
Run-time error '1004':
This selection isn't valid. Make sure the copy and paste areas don't overlap unless they are the same size.

The highlighted area is in bold below:
If .Value Like "*Yes*" Then .EntireRow.Cut Destination:=Sheets("Closed").Range("K" & Rows.Count).End(xlUp).Offset(1, -2)

 

[Fluff]

Change it to

If .Value Like "*Yes*" Then .EntireRow.Cut Destination:=Sheets("Closed").Range("K" & Rows.Count).End(xlUp).Offset(1, -10)

 

[bobalouie76]

Fluff,
Thank you so much.. that worked!

 

[Fluff]

Glad to help & thanks for the feedback

 

[Jmoz092]

Hi, I've been reading the forums for a while but have never posted. I have a similar issue and I've tried adjusting my VBA code to suit my needs as well...to no avail.
I have a workbook with sheets listed as months(Jan,Feb<mar,etc). Column M has data validation to select "Yes" or "Ordered" to denote whether product has been given or ordered. I want to search each monthly sheet (there are other sheets that summarize) for any "Ordered" entered into column M and have the data from every other column in that row displayed on a seperate sheet on the workbook (Sheet 2).

This is my "revision" of the code used in this thread...

Sub LookForOrderedStockings()
Dim LR As Long, i As Long
With Sheets("Ordered")
LR = .Range("M" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With .Range("M" & i)
If .Value Like "*Ordered*" Then .EntireRow.Cut Destination:=Sheet2("Ordered").Range("M" & Rows.Count).End(xlUp).Offset(1, -10)
End With
Next i
End With
End Sub

I run the macro on Sheet 2 and nothing is happening. I currently have 2 "Ordered" cells within my workbook. Any help would be appreciated.

 

[Jmoz092]

Is there a way to make this macro run automatically (when the workbook is opened), if I can get it to run as intended?