inserted copied range into different worksheets

[lcheni]

I was trying to code VBA to copy a dynamic range from Worksheet 1,
count the number of rows of the range (single column), then in
Worksheet 2, tried to insert the counted number of rows beginning at
the selected cell before pasting the selected range.
,,,
ActiveCell.Resize(n).EntireRow.Insert
activecell.paste

However, after the row insert, the range is pasted all the way across
the worksheet (same data coped to the column IV).

===================================================
I found that I needed to revise the codes to the below:
1) from Wksht 1, located the range, count the rows involved
2) go to Wksht 2, insert the rows needed
3) return to Wksht 1. copy the range
4) back to Wksht 2, pasted the range .

Are there some easy ways to do this?
Thanks.

 

[Dave Peterson]

How do you determine that range to copy? Is it based on what the user wants or
something else?

If it's based on the user selecting the range, you could have your macro do the
work of asking.

Option Explicit
Sub testme()

Dim RngToCopy As Range
Dim DestCell As Range

Set RngToCopy = Nothing
On Error Resume Next
Set RngToCopy = Application.InputBox _
(Prompt:="Please select a single area/column range", _
Default:=Selection.Areas(1).Columns(1).Address, _
Type:=8).Areas(1).Columns(1)
On Error GoTo 0

If RngToCopy Is Nothing Then
Exit Sub 'user hit cancel
End If

Set DestCell = Nothing
On Error Resume Next
Set DestCell _
= Application.InputBox _
(Prompt:="Please select a single cell to paste below", _
Type:=8).Cells(1)
On Error GoTo 0

If DestCell Is Nothing Then
Exit Sub 'user hit cancel
End If

DestCell.Offset(1, 0).Resize(RngToCopy.Cells.Count, 1).EntireRow.Insert

RngToCopy.Copy _
Destination:=DestCell.Offset(1, 0)

End Sub