How to return an activecell to a specific column

[burnin2emax]

Hi, hope someone can help.
I want to create a macro that will insert a row specified by the cursor.
And in this newly created row, I want to copy a range of formulas.

If I do it as specified below,it will paste the values beginning from the cursor that was initially selected. What additional command should I add so that the formulas are copied to a specified column of the newly created row.
Or is there any simpler ways to do it.

ActiveCell.EntireRow.Insert
n=ActiveCell.Address
Range("Sheet2!E2:L2").Copy Range

Thanks in advance.

-Excel Noob-

 

[Andrew Poulsom]

What do you want to copy where?

 

[BrianB]

Not clear where you want the formulas to go.

The full method for the copy line is something like :-

 Worksheets("Sheet2").Range("E2:L2").Copy _
     Destination:=Worksheets("Sheet1").Range("A1")

 

[burnin2emax]

Macro to copy formulas to a newly created row

Oops. Sorry for being unclear. I'll try better this time.

This is what I intend to do :
1. Click at a cell
2. Add an entire row to the cell selected earlier.
3. Copy formulas from a range to this newly created row at a specific column range.

Hope it's clearer this time. Help me please!

-Excel Noob-

 

[Andrew Poulsom]

Re: Macro to copy formulas to a newly created row

Oops. Sorry for being unclear. I'll try better this time.

This is what I intend to do :
1. Click at a cell
2. Add an entire row to the cell selected earlier.
3. Copy formulas from a range to this newly created row at a specific column range.

Hope it's clearer this time. Help me please!

-Excel Noob-

Not any clearer really. What range do you want to copy where?

 

[burnin2emax]

Arrggh....
I'll try again.

This is the situation I am in :
1. I have data and formulas in worksheet 2.
2. I am using VLOOKUP to refer to some values in worksheet 2.

What I want a macro in worksheet 1 to do, when executed:
1. Add an entire row to the cell that I clicked on.(Before I click the command button to run the macro)
2. Copy formulas from a range to this newly created row at a specific column range.

An example will be to copy range("E2:L2") of worksheet 2 to column B of the newly created row in worksheet 1.


Hope someone can understand me this time...

Thanks ppl...

 

[Andrew Poulsom]

Try:

ActiveCell.EntireRow.Insert 
Worksheets("Sheet2"".Range("E2:L2").Copy Range("B" & ActiveCell.Row)

 

[burnin2emax]

Yeah man... It work wonders. You are the man..!!! Thanks.

But, just would like to know, is there any added advantage of using

Worksheets("Sheet2"".Range("E2:L2").Copy Range("B" & ActiveCell.Row)

over 

Range("Sheet2!E2:L2").Copy Range("B" & ActiveCell.Row)

as both seems to work just as fine.
Thanks, many thanks man...

 

[Andrew Poulsom]

My code should of course have been:

Worksheets("Sheet2").Range("E2:L2").Copy Range("B" & ActiveCell.Row)

I prefer to use the Worksheets property to return a worksheet, but it's just personal preference. Either method is fine.