Copy paste a row on the next sheet based on the value [SOLVED]

[dev.sutradhar1987]

I have a Sheet Named "Sheet1" where in column G i have a dropdown where if i select "Completed" then the entire row should be copied to the next sheet"Sheet2". Then after that if again a task is completed it should get copied to the next sheet in the next empty row and son on.

I have tried an got this macro but not working exactly.

[pre]

Sub SearchForString()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 4
LSearchRow = 2

'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

'If value in column E = "Mail Box", copy entire row to Sheet2
If Range("F" & CStr(LSearchRow)).Value = "Completed" Then

'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into Sheet2 in next row
Sheets("Sheet2").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching
Sheets("Sheet1").Select

End If

LSearchRow = LSearchRow + 1

Wend

'Position on cell A3
Application.CutCopyMode = False
Range("A2").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub

[/pre]

 

[Luke M]

From your problem description, it sounded like you wanted the copying to be done in real-time as the dropdown in col G is changed. If that is correct, I'd suggest using this event macro. Right click on the sheet tab, view code, and paste this in:

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
'We'll use a change event macro so things get done automatically

'Frist, did a cell of itnerest change?
If Intersect(Target, Range("G:G")) Is Nothing Or Target.Count > 1 Then Exit Sub
If UCase(Target.Value) = "COMPLETED" Then
'Let's do something!
Application.EnableEvents = False

'Which sheet are we pasting to?
With Worksheets("Sheet2")
'This single line does our copy and paste
'and also finds the next empty row by starting at bottom of sheet
'Doing a Ctrl+up command to find first cell w/ text
'and then doing an offset of 1 row (to be in the empty cell)
Target.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

Application.EnableEvents = True
End If
End Sub

[/pre]

 

[dev.sutradhar1987]

Thank You Luke M..It works fine. I also wanted that after copy it will delete the rows in sheet 1. I have done that myself..Thanks

 

[dev.sutradhar1987]

Hey Like M, Could you help me with this..


Hi,


I want a macro which will record time for completing a particular task in Excel 2007.


The Concept is :-


Suppose Column A has a DropDown with 3 options(In Progress, Hold, Completed) and time will be calculated and shown in Column B

Conditions:

1. If In Progress TIME Starts

2. If Hold TIME Pauses.

3. If Completed TIME Stops and Calculated the Total Time Taken from (In Progress - Completed) and Stores the Final Time In Column B (Time Format HH:MM:SS)


Please could you help me out in this.


Waiting for your Earliest Feedback.

 

[Debraj]

Hi Dev,


I assume.. Actual Post is solved..


Regarding.. 2nd query.. Did you checked your another thread..

If not, please check & start following there..

http://chandoo.org/forums/topic/macro-which-will-record-time-for-completing-a-particular-task-in-excel-2007


Regards,

Deb