Can anyone do this?
 

Hi everyone,
My problem is this; I have two worksheets (1 and 2) and on Sheet 1 I have
data about 'work in hand'. What I would like to do is when the work data on
any one row is marked as completed is simply hit a macro button that will cut
the entire row and paste it to Sheet 2 on the next available row/cell (A1, B1
etc), return the user to sheet 1 and then delet the (now) blank row. Any
ideas, please?

Can anyone do this?
 

You could try something like the following...you will have to change the
range to look at what ever field you mark as complete.

Dim oWorkSheet As Worksheet

For i = 1 To 65536
If Range("A" + CStr(i)).FormulaR1C1 = "complete" Then
Set oWorkSheet = ActiveWorkbook.Sheets("Sheet2")
Rows(CStr(i) + ":" + CStr(i)).Select
Application.CutCopyMode = False
Selection.Cut
oWorkSheet.Activate
oWorkSheet.Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Set oWorkSheet = ActiveWorkbook.Sheets("Sheet1")
oWorkSheet.Select
Rows(CStr(i) + ":" + CStr(i)).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
Next

"Mel Smith" wrote:

Hi everyone,
My problem is this; I have two worksheets (1 and 2) and on Sheet 1 I have
data about 'work in hand'. What I would like to do is when the work data on
any one row is marked as completed is simply hit a macro button that will cut
the entire row and paste it to Sheet 2 on the next available row/cell (A1, B1
etc), return the user to sheet 1 and then delet the (now) blank row. Any
ideas, please?

Can anyone do this?
 

Jim, I tried a small adaption but still no joy:
Dim oWorkSheet As Worksheet, r As Integer, c As String

r = InputBox("Select Row Number")
c = MsgBox("Cut & Move?", vbYesNo)

For i = 1 To 65536
If Range(r + CStr(i)).FormulaR1C1 = vbYes Then
Set oWorkSheet = ActiveWorkbook.Sheets("Sheet2")
Rows(CStr(i) + ":" + CStr(i)).Select
Application.CutCopyMode = False
Selection.Cut
oWorkSheet.Activate
oWorkSheet.Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Set oWorkSheet = ActiveWorkbook.Sheets("Sheet1")
oWorkSheet.Select
Rows(CStr(i) + ":" + CStr(i)).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
Next

"Jim F" wrote:

You could try something like the following...you will have to change the
range to look at what ever field you mark as complete.

Dim oWorkSheet As Worksheet

For i = 1 To 65536
If Range("A" + CStr(i)).FormulaR1C1 = "complete" Then
Set oWorkSheet = ActiveWorkbook.Sheets("Sheet2")
Rows(CStr(i) + ":" + CStr(i)).Select
Application.CutCopyMode = False
Selection.Cut
oWorkSheet.Activate
oWorkSheet.Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Set oWorkSheet = ActiveWorkbook.Sheets("Sheet1")
oWorkSheet.Select
Rows(CStr(i) + ":" + CStr(i)).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
Next

"Mel Smith" wrote:

Hi everyone,
My problem is this; I have two worksheets (1 and 2) and on Sheet 1 I have
data about 'work in hand'. What I would like to do is when the work data on
any one row is marked as completed is simply hit a macro button that will cut
the entire row and paste it to Sheet 2 on the next available row/cell (A1, B1
etc), return the user to sheet 1 and then delet the (now) blank row. Any
ideas, please?