Move information from one sheet to another sheet within the same w

J

Jim

I have a workbook that consist of two sheets. The first sheet is a list of
open projects. As I complete the projects I would like that row or project
information to be moved to another sheet titled completed projects. All of
the project information will appear in one row. ie

project name date started date completed
abc project 5/15/04 8/15/04
 
R

Ron de Bruin

Hi Jim

Sheet1 is the data sheet and sheet2 is the database sheet.
This example will copy the activecell row

Sub copy_3()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Lr = LastRow(Sheets("Sheet2")) + 1
Set sourceRange = ActiveCell.EntireRow
Set destrange = Sheets("Sheet2").Rows(Lr)
sourceRange.Copy destrange
sourceRange.EntireRow.Delete
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

For more examples look here
http://www.rondebruin.nl/copy1.htm
 
J

Jim May

Ron:
I've put this Function in a standard module and have gone to my sheet1
cell F130 and entered 123. Then back to cell A1 where I enter
=Lastrow("sheet1")
but I get the #VALUE! result rather than the expected 130. I'm missing
something here?;
Can you assist?

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
 
R

Ron de Bruin

Hi Jim

A worksheet function can't move rows to other sheets.

You must use the macro I posted
Copy the function and macro in a normal module

Select a cell in the row you want to copy in the sheet named Sheet1.
Run the macro copy_3 and you will see that it will copy the row to the sheet named Sheet2 on the
first empty row and delete the row on Sheet1
 
J

Jim May

Ron:
Thanks for the reply; I note was actually too short. After looking over
your reply to the OP - I decided as frequently as we all want and need to
know the LastRow in a sheet
perhaps your UDF Function Lastrow() mgiht be the trick. If so I'd put it
into my personal.xls and use it when needed. So it is from that (only)
prespective that I was asking about the Function Lastrow and not any of the
other things the OP was trying to solve. Can the Function Lastrow() stand
on its own as I am trying to make it?
Jim
 
R

Ron de Bruin

Hi Jim (confusing two Jim's)

Yes you can use it on its own with VBA code
MsgBox LastRow(ActiveSheet)


If you want it as a worksheet function then look at Harlan's example
Use this on a worksheet

=lr()

Function lr(Optional r As Range) As Variant
Dim ur As Range, c As Range, i As Long, n As Long

'there arguments for & against volatility - optional to uncomment
Application.Volatile

If r Is Nothing Then Set r = Application.Caller
If Not TypeOf r Is Range Then Set r = ActiveCell

Set ur = r.Parent.UsedRange
n = ur.Rows.Count

For i = n To 1 Step -1
Set c = ur.Cells(i, 1)
If Not IsEmpty(c.Value) Then Exit For
If Not IsEmpty(c.End(xlToRight).Value) Then Exit For
Next i

lr = ur.Row + i - 1
End Function
 
J

Jim May

nice one, thanks Ron
Jim

Ron de Bruin said:
Hi Jim (confusing two Jim's)

Yes you can use it on its own with VBA code
MsgBox LastRow(ActiveSheet)


If you want it as a worksheet function then look at Harlan's example
Use this on a worksheet

=lr()

Function lr(Optional r As Range) As Variant
Dim ur As Range, c As Range, i As Long, n As Long

'there arguments for & against volatility - optional to uncomment
Application.Volatile

If r Is Nothing Then Set r = Application.Caller
If Not TypeOf r Is Range Then Set r = ActiveCell

Set ur = r.Parent.UsedRange
n = ur.Rows.Count

For i = n To 1 Step -1
Set c = ur.Cells(i, 1)
If Not IsEmpty(c.Value) Then Exit For
If Not IsEmpty(c.End(xlToRight).Value) Then Exit For
Next i

lr = ur.Row + i - 1
End Function
Click to expand...
 
Top