Copy to Next Blank Row

[Jeff Gross]

I need to copy from one worksheet to the next blank row on another worksheet
but I keep getting an error on the below code at the line "Lr =
lastrow(DestSheet)". The error is a compile error (sub or function not
defined). Can anyone help?

Thanks ahead.

Sub copy_1()
Dim SourceRange As Range
Dim DestRange As Range
Dim DestSheet As Worksheet
Dim Lr As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set SourceRange = Sheets("Data Sort 1b").Range("A2300")
Set DestSheet = Sheets("Data Sort 1c")
Lr = lastrow(DestSheet)

Set DestRange = DestSheet.Range("A" & Lr + 1)
SourceRange.Copy DestRange

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

 

[Dave Peterson]

Go back to Ron's site (where you got this code) and look for the function.
Either it's on the same page or there's a link to it on the page where it was
used.

 

[Jim Thomlinson]

LastRow is not a defined function in VBA. You need to write your own
function. Here is one that I use to find the last cell in a worksheet. Try
this...

Sub copy_1()
Dim SourceRange As Range
Dim DestRange As Range
Dim DestSheet As Worksheet
Dim Lr As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set SourceRange = Sheets("Data Sort 1b").Range("A2300")
Set DestSheet = Sheets("Data Sort 1c")
Lr = LastCell(DestSheet).row

Set DestRange = DestSheet.Range("A" & Lr + 1)
SourceRange.Copy DestRange

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim lngLastColumn As Long

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
lngLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
lngLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, lngLastColumn)
End Function

 

[Rick Rothstein]

I have found that these simpler Find methods work just as well for finding
the last row and column with data...

LastUsedRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row

LastUsedCol = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

You can, of course, change the ActiveSheet reference to a specific worksheet
reference; so, in your LastCell function code, these statements would
become...

lngLastRow = wks.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row

lngLastColumn = wks.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

 

[Ron de Bruin]

You can read this on top of the page
http://www.rondebruin.nl/copy1.htm

Important: The macro examples use one function or two functions that
you can find in the last section of this page.

 

[Jim Thomlinson]

Thanks... I have been using that code for so long without looking at it that
I never really noticed. One thing I have change din the past was xlformulas
and xlvalues so here is my new code...

Public Function LastCell(Optional ByVal wks As Worksheet, _
Optional ByVal blnConstantsOnly As Boolean) As Range
Dim lngLastRow As Long
Dim lngLastColumn As Long
Dim lngLookIn As Long

If blnConstantsOnly = True Then
lngLookIn = xlValues
Else
lngLookIn = xlFormulas
End If

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
LookIn:=lngLookIn, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
lngLastColumn = wks.Cells.Find(What:="*", _
LookIn:=lngLookIn, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
lngLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, lngLastColumn)
End Function