transfer macro

  • Thread starter Thread starter Warm
  • Start date Start date
W

Warm

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
 
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).Row
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
 
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
 
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!?
 
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 ?
 
Set rng = Range("T1:T" & lr)
For Each c In rng
If c = "x" Then
do the copy part


Gord Dibben MS Excel MVP
 
Back
Top