Perhaps most easiest question

**Andris** · 10-11-2006, 11:57 AM

Hi all!

I try to create a macro, which should do the following:
1. Mark a range (for example A1:C3)
2. In selected cell insert the copied cells (marked range) by shifting the cells down.

The problem: by recording the macro excel fixes just one paste position but I want to be able to paste a marked range in any chosen position.

Any solutions?
Thanks!

**Myles** · 10-11-2006, 12:07 PM

Andris,

I wish to give a you a friendly advice. The tone of the title of a request counts for a lot. Choosing a heading such as yours will only serve to turn off potential helpers on the forum. If you honestly believe that your request calls for the easiest of solutions, then you might as well pause for a moment and crack the solution yourself!

Coming to your question, could you please relate what you exactly want to do. There might be a better solution than what the Macro Recorder can produce - which is always very earthy and crude.

**Andris** · 10-12-2006, 03:13 AM

Thanks for your advice, Myles, you are absolutely true about the importance of the tone... But by reading all the discussions here, I have a feeling of beeing the only beginner within excel-gurus. That's about the feeling by giving the title... I'm sorry...

About what I want to create. I try to describe it...
I have a table:

Name Age (Subjects)
... ... (Place to fill)

Then there's a lot of text

Now again I want to insert my table
Name Age
.... ....
Of course, I could simply copy it from above but that's not the best solution in that case (Perhaps I have filled the table already with the name and age.) So I thought about a macro which copies the "empty" table just with subjects and pastes in the active cell by shifting the rows down.
So I made my macro...

Range("A1:B2").Select
Selection.Copy
Range("G1").Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub

The problem is the fixed range "G1". I want to be able to insert the copied range in any active (selected) cell.

In VB editor I tried something like replacing
Range("G1").Select
with
Copy Destination:=ActiveCell

It still doesn't function.
I hope you can understand my explanation.

Have you any advice, please?

**Myles** · 10-12-2006, 08:58 AM

Andris,

I'm not sure if you got you right. From your macro recording, it seems as if you want to copy the range A1:B2 to D1:E2. If that is the case,

Range("a1:b2")..Copy Range("D1")

**Andris** · 10-12-2006, 09:18 AM

Myles, thanks for your answer.
It seems I haven't explained my problem clear...

I'd like to copy one selected range into the active cell. Or better said I want to activate one cell and have a macro "insert_range_1". Then the macro copies this range 1, and inserts it in the active cell by shifting the rows down (for not overwriting the information under selected cell) (If my Range_1 is A1:D4, my macro should insert 4 rows).

It would be easy to show it, but to explain it on this way

**Myles** · 10-13-2006, 07:46 AM

Andris,

It is still hard to follow. However, the best that I have gleaned from all that is that you want to copy Range("A1:B2") and "insert (sic)" it in Range("G1") such that the original contents of G1 will be preserved, hence the need to shift down rows. For this scenario:

Sub Copyy()
Range("g1:h2").Insert Shift:=xldown
Range("a1:b2").Copy Range("g1")
End Sub

The above code copies A1:B2 to G1:H2 while the original value in G1 shifts to G3.

Perhaps most easiest question

LinkBack

Thread Tools

Rate This Thread

Display

macro for copying the range

better

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions