Hi
I have thefollowing macro to transfer information from one sheet to anothe
however it doesnt like the 'Lr = lastRow("DestSheet")' can anyone please help?
Sub Transfer()
Dim SourceRange As Range, DestRange As Range
Dim DestSheet As Worksheet, Lr As Long

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

Set SourceRange = Sheets("Outstanding").Range("a13:T13")

Set DestSheet = Sheets("Paid")
Lr = lastRow("DestSheet")

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

SourceRange.Copy
DestRange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False

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

End Sub

--
Warm

Hi,

Try this

Sub Transfer()
Dim SourceRange As Range, DestRange As Range
Dim DestSheet As String, Lr As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set SourceRange = Sheets("Outstanding").Range("a13:T13")
DestSheet = "Paid"
Lr = Sheets(DestSheet).Cells.SpecialCells(xlLastCell).R ow
Set DestRange = Sheets(DestSheet).Range("A" & Lr + 1)
SourceRange.Copy
DestRange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


Mike

"Warm" wrote:

Hi
I have thefollowing macro to transfer information from one sheet to anothe
however it doesnt like the 'Lr = lastRow("DestSheet")' can anyone please help?
Sub Transfer()
Dim SourceRange As Range, DestRange As Range
Dim DestSheet As Worksheet, Lr As Long

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

Set SourceRange = Sheets("Outstanding").Range("a13:T13")

Set DestSheet = Sheets("Paid")
Lr = lastRow("DestSheet")

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

SourceRange.Copy
DestRange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False

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

End Sub

--
Warm

Hi,
i have tried the macro below however i am now getting a compile warning:
'Only comments may appear after end sub, end function or end property!?


--
Warm


"Mike H" wrote:

Hi,

Try this

Sub Transfer()
Dim SourceRange As Range, DestRange As Range
Dim DestSheet As String, Lr As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set SourceRange = Sheets("Outstanding").Range("a13:T13")
DestSheet = "Paid"
Lr = Sheets(DestSheet).Cells.SpecialCells(xlLastCell).R ow
Set DestRange = Sheets(DestSheet).Range("A" & Lr + 1)
SourceRange.Copy
DestRange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


Mike

"Warm" wrote:

Hi
I have thefollowing macro to transfer information from one sheet to anothe
however it doesnt like the 'Lr = lastRow("DestSheet")' can anyone please help?
Sub Transfer()
Dim SourceRange As Range, DestRange As Range
Dim DestSheet As Worksheet, Lr As Long

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

Set SourceRange = Sheets("Outstanding").Range("a13:T13")

Set DestSheet = Sheets("Paid")
Lr = lastRow("DestSheet")

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

SourceRange.Copy
DestRange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False

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

End Sub

--
Warm

looks like your macro is looking for a private function named
"lastrow" and cannot find it
instead of
Set DestSheet = Sheets("Paid")
Lr = lastRow("DestSheet")

use:
Set DestSheet = Sheets("Paid")
Sheets("Paid").Activate
Lr = ActiveCell.SpecialCells(xlCellTypeLastCell).Row

HIH

On 28 Maj, 10:50, Warm wrote:
Hi
I have thefollowing macro to transfer information from one sheet to anothe
however it doesnt like the 'Lr = lastRow("DestSheet")' can anyone please help?
Sub Transfer()
Dim SourceRange As Range, DestRange As Range
Dim DestSheet As Worksheet, Lr As Long

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

Set SourceRange = Sheets("Outstanding").Range("a13:T13")

Set DestSheet = Sheets("Paid")
Lr = lastRow("DestSheet")

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

SourceRange.Copy
DestRange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False

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

End Sub

--
Warm

Sorry got it working now. Sorry to be a pain but is there any way i can
modify it for another sheet so that it only transfers the information if
there is an 'x' in column
T ?
--
Warm


"Warm" wrote:

Hi
I have thefollowing macro to transfer information from one sheet to anothe
however it doesnt like the 'Lr = lastRow("DestSheet")' can anyone please help?
Sub Transfer()
Dim SourceRange As Range, DestRange As Range
Dim DestSheet As Worksheet, Lr As Long

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

Set SourceRange = Sheets("Outstanding").Range("a13:T13")

Set DestSheet = Sheets("Paid")
Lr = lastRow("DestSheet")

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

SourceRange.Copy
DestRange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False

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

End Sub

--
Warm

Set rng = Range("T1:T" & lr)
For Each c In rng
If c = "x" Then
do the copy part


Gord Dibben MS Excel MVP

On Thu, 28 May 2009 07:41:03 -0700, Warm
wrote:

Sorry got it working now. Sorry to be a pain but is there any way i can
modify it for another sheet so that it only transfers the information if
there is an 'x' in column
T ?