H Mifty,

This may help. Put some data in column N. Copy and paste this in the sheet
module and then run it.

Dim lastrow As Long
Sub LastRowNo()
lastrow = Worksheets(1).Cells(Rows.Count, "N").End(xlUp).Row
MsgBox lastrow
End If

You should get a message box with a number in it which is the same as the
last row of column N that has a value in it. Lets say the message box
return 26.

Now since lastrow = 26, this line of code from Gary's solution:
Range("E3:N" & lastrow).Copy

is the same as Range("E3:N26").Copy

So, when the length of data in column N changes the the range that is to be
copied changes with it.

HTH
Regards,
Howard


"Mifty" <Mifty@discussions.microsoft.com> wrote in message
news:EB2A7969-A7E0-48B9-B8CD-E3BBF2AEF2A9@microsoft.com...
> Hi,
> I'm very new to all this and used the record macro function to copy part
> of
> a range of data from one workbook to another using paste values only.
> Being a
> complete beginner I was so pleased when it worked beautifully (my first
> time
> :-) ) but then realised that the range will change each month as more data
> added.
>
> Could anyone please suggest a way to edit this macro so that it recognises
> the end of the data entry. There are formulae in columns A, E and I-N in
> the
> source workbook
>
> Sub Macro1()
> '
> ' Macro1 Macro
> ' Macro recorded 25/02/2006 by Mifty
> '
> ' Keyboard Shortcut: Ctrl+a
> '
> Range("E3:N10").Select
> Selection.Copy
> Workbooks.Open Filename:= _
> "C:\Documents and Settings\TEMP\My Documents\Destination.xls"
> Range("A3").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> End Sub
>
> Many thanks
> --
>
> Mifty