ExcelBanter - View Single Post

Richard Buttrey

On 31 Mar 2005 07:41:11 -0800, wrote:

ok this is what i have

Sub Macro7()
'
' Macro7 Macro
' Macro recorded 2005/03/31 by asmith2
'

'
Range("A9:E9").Select
selection.End(xlDown).Select
Range("sheet3").Select
ActiveSheet.Paste
End Sub

and the error i get selects selection and says "Compile Error: Expected
Function or variable"

now i'm lost

Three of things.
1.Try and avoid .Select and the similar .Activate command for
worksheets. With large applications this can slow things down. It's
usually possible to refer to cells/ranges directly by name and have
the macro do its stuff without selecting.

2. Is the range you're trying to select a contiguous range? i.e no
completely blank rows or blank columns? If so I always use the
..CurrentRegion property in VBA. This does away with all the end down
stuff.

3. Try and avoid hard-coding range references like "A9:E9". As you
build on your application - as you surely will, you'll probably move
this somewhere else, and then will have to change your code - if you
remember. Always give names to ranges.

So, with the above in mind do the following.

1. Go to A9 and give it a name - say "DataTop"
2. Go to the cell in sheet 3 where you want to paste the copy of your
data and give this a name - say "MyCopy"

3. Now the only code you need is as follows:

Sub Macro7()

Range("Datatop").CurrentRegion.CopyDestination:=Ra nge("Mycopy")
End Sub

Hope this helps. if 2 above doesn't apply then let me know.

Regards

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________