transfer macro

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
 
M

Mike H

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
 
J

Jarek Kujawa

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
 
W

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!?
 
W

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 ?
 
G

Gord Dibben

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


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy to Next Blank Row 5
Copy to last row macro 4
this code crashes excel. How come? 3
Arrays and Loops 3
Weekly Totals 6
Paste Special 2
Lastrow - function not defined 2
Having trouble with this code 13

Top