Copy to Next Blank Row
 

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("A2:D300")
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

Copy to Next Blank Row
 

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.



Jeff Gross wrote:

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("A2:D300")
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

Copy to Next Blank Row
 

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("A2:D300")
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
--
HTH...

Jim Thomlinson


"Jeff Gross" wrote:

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("A2:D300")
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

Copy to Next Blank Row
 

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

--
Rick (MVP - Excel)


"Jim Thomlinson" wrote in message
...
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("A2:D300")
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
--
HTH...

Jim Thomlinson


"Jeff Gross" wrote:

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("A2:D300")
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

Copy to Next Blank Row
 

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.


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Jeff Gross" wrote in message ...
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("A2:D300")
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

Copy to Next Blank Row
 

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
--
HTH...

Jim Thomlinson


"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Jim Thomlinson" wrote in message
...
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("A2:D300")
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
--
HTH...

Jim Thomlinson


"Jeff Gross" wrote:

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("A2:D300")
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