copying changing range
untested, bu maybe something like this
Option Explicit
Dim lastrow As Long
Sub Macro1()
lastrow = Worksheets(1).Cells(Rows.Count, "N").End(xlUp).Row
'
' Macro1 Macro
' Macro recorded 25/02/2006 by Mifty
'
' Keyboard Shortcut: Ctrl+a
'
Range("E3:N" & lastrow).Copy
Workbooks.Open _
Filename:="C:\Documents and Settings\TEMP\My Documents\Destination.xls"
Range("A3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub
--
Gary
"Mifty" wrote in message
...
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
|