Hi,

Please write a macro to CHANGE number of rows €œ3€� to a variable range
(end.xlDown) on the macro below.

Sub GotToRangeResizeCopyPaste()

Application.Goto Reference:="MyRange"
ActiveCell.Resize(3, 10).Select
Selection.Copy
Range("OtherRange").Select
ActiveSheet.Paste

End Sub

Thanks

Unless you have a specific reason for activating a the first sell and
selecting the second (which are actually different operations), i
wouldnt bother. The following accomplished what you want to do in one
line:

Range("myrange", Range("myrange").End(xlDown)).Copy Range("otherrange")

Regards, Sam

Hi Sam,

Thank you for your response. I always need the macro to:

Sub GotToRangeResizeCopyPaste()

Application.Goto Reference:="MyRange" 'Go to a SELECTED range (no
name/unknown)
ActiveCell.Resize(3, 10).Select 'Resize it
Selection.Copy
Range("OtherRange").Select '(3) Go to a SELECTED range (no name/unknown)
ActiveSheet.Paste

End Sub

Please help. Thank you.


"Sam" wrote:

Unless you have a specific reason for activating a the first sell and
selecting the second (which are actually different operations), i
wouldnt bother. The following accomplished what you want to do in one
line:

Range("myrange", Range("myrange").End(xlDown)).Copy Range("otherrange")

Regards, Sam

so if i understand, you want to look at the current selection, and copy
an area 10 columns across and n rows down (n= number of used rows under
the selection) and past in a range called 'otherrange'?:

Range(ActiveCell.Range("a1"), ActiveCell.End(xlDown).Offset(0,
10)).Copy Range("otherrange")

Hi Sam,

Thanks again for your response.

Actually, what I'm looking for is how to write a macro for the variable
(xlDown)of "3" (rows) on the macro below

ActiveCell.Resize(3, 10).Select

I need this macro for a lot of things because I name a range to go to, use
the REVISED macro above, then, I can use it for a lot of things, like copy,
put borders, insert rows, etc. I have been trying to get the right macro for
this.

Thanks again.



"Sam" wrote:

so if i understand, you want to look at the current selection, and copy
an area 10 columns across and n rows down (n= number of used rows under
the selection) and past in a range called 'otherrange'?:

Range(ActiveCell.Range("a1"), ActiveCell.End(xlDown).Offset(0,
10)).Copy Range("otherrange")

I'm confused as the others, but maybe....

Dim myRng as range
with activesheet
set myrng = .range("myRange")
'this doesn't make much sense to me
'but it shows how to find the number of rows/columns in myrng
myrng.resize(myrng.rows.count,myrng.columns.count) .select
'or
'make it 10 columns wide
myrng.resize(myrng.rows.count,10).select
'which would be equivalent to:
myrng.resize(,10).select
end with

(if you specify the rows/columns to resize, then it won't be changed.)

Closer???


????? wrote:

Hi Sam,

Thanks again for your response.

Actually, what I'm looking for is how to write a macro for the variable
(xlDown)of "3" (rows) on the macro below

ActiveCell.Resize(3, 10).Select

I need this macro for a lot of things because I name a range to go to, use
the REVISED macro above, then, I can use it for a lot of things, like copy,
put borders, insert rows, etc. I have been trying to get the right macro for
this.

Thanks again.

"Sam" wrote:

? so if i understand, you want to look at the current selection, and copy
? an area 10 columns across and n rows down (n= number of used rows under
? the selection) and past in a range called 'otherrange'?:
?
? Range(ActiveCell.Range("a1"), ActiveCell.End(xlDown).Offset(0,
? 10)).Copy Range("otherrange")
?
?

--

Dave Peterson