I'm trying to make a macro that selects a given number of columns and
a variable number of rows and then copys it into a new work sheet. For
some reason no matter what i do though it never works though it always
stops at this line.

Range(selection, selection.End(xlDown)).Select

Here is the start of the macro. If you could help it would be greatly
appreciated. There isen't much here but i didn't want to program to
much more before i figured out this problem.


Andrew

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

'
Range("A9:E9").Select
Range(selection, selection.End(xlDown)).Select
selection.Copy
End Sub

Range(selection, selection.End(xlDown)).Select

Change it to read as follows

Selection.End(xlDown).Select



'
' Macro7 Macro
' Macro recorded 2005/03/31 by asmith2
'

'
Range("A9:E9").Select
Range(selection, selection.End(xlDown)).Select
selection.Copy
End Sub

Ignore the previous post, did not realise you also selected cols.

Your code is actually correct, except that you stop after copying. Why not
code in the following, and see what happens:

Range("<Your destination").Select
ActiveSheet.Paste

"Andrew T Smith" wrote:

I'm trying to make a macro that selects a given number of columns and
a variable number of rows and then copys it into a new work sheet. For
some reason no matter what i do though it never works though it always
stops at this line.

Range(selection, selection.End(xlDown)).Select

Here is the start of the macro. If you could help it would be greatly
appreciated. There isen't much here but i didn't want to program to
much more before i figured out this problem.


Andrew

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

'
Range("A9:E9").Select
Range(selection, selection.End(xlDown)).Select
selection.Copy
End Sub

This is what I have now but it still breaks on the same error

Range("A9:E9").Select
selection.End(xlDown).Select
selection.Copy

Andrew

This would work but I have an a variable number of rows that i'm trying
to copy.

This is where the Selection.End(xldown).Select comes in? You have selected a
specific number of columns, and with end down you are selecting the number of
used rows, down to the last used one.

Unless you have empty rows in between your range, of course, in which case
you will have to get rid of those first.

" wrote:

This would work but I have an a variable number of rows that i'm trying
to copy.

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

See comments in your code, without the

" 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
Change this to
Range(Selection, 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

Range(selection, selection.End(xlDown)).Select

still gives me the same error

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
__________________________

I'm still rather new to VBA coding. How would i go about naming those
cells

Select the cells you want to name. Do an Insert, Name, Define. Type the
name into the box at the top.
It's a good idea to take an existing spreadsheet, or just create a
dummy one, with row names over on the left, column names on the right.
Then experiment with selecting different choices of rows and columns,
some with the headers and some not, the do Insert, Name, Define or
Create or Apply. Different ones do slightly different things.
After you've named some ranges, you can do an Edit, GoTo command, and
select any named range and XL will select that range. You can also
click on the pulldown arrow in the top left of the spreadsheet, below
the toolbars, where the current cell address is shown. All the range
names in that sheet will be there. If you select one, that range will
be selected by XL.
Finally, you can use them in formulas and VBA. If you want a cell to
display the value at the intersection of 2 named ranges, say "North"
and "December", the formula would be =North December. Pretty simple.
Of course, like any other text based thing, range names need to be in
quotes in VBA.
Hope this helps.

with row names over on the left, column names on the right.

Meant to say column names at the top!

thanks :D