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

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("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

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
--
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("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

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" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message
news:25F9A623-B1DC-43C9-91FD-(E-Mail Removed)...
> 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
> --
> 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("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

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" <(E-Mail Removed)> wrote in message news:068F86AA-EF05-4E21-8E8D-(E-Mail Removed)...
>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

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" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message
> news:25F9A623-B1DC-43C9-91FD-(E-Mail Removed)...
> > 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
> > --
> > 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("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
>
>