How to return an activecell to a specific column

burnin2emax

New Member
Joined
Nov 9, 2005
Messages
10
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(n)

Thanks in advance.

-Excel Noob-
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Not clear where you want the formulas to go.

The full method for the copy line is something like :-
Code:
 Worksheets("Sheet2").Range("E2:L2").Copy _
     Destination:=Worksheets("Sheet1").Range("A1")
 
Upvote 0

burnin2emax

New Member
Joined
Nov 9, 2005
Messages
10
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-
 
Upvote 0

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
ADVERTISEMENT
Re: Macro to copy formulas to a newly created row

burnin2emax said:
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?
 
Upvote 0

burnin2emax

New Member
Joined
Nov 9, 2005
Messages
10
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... :cry:

Thanks ppl...
 
Upvote 0

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
ADVERTISEMENT
Try:

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

burnin2emax

New Member
Joined
Nov 9, 2005
Messages
10
Yeah man... It work wonders. You are the man..!!! Thanks.

But, just would like to know, is there any added advantage of using
Code:
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...
 
Upvote 0

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 
Upvote 0

Forum statistics

Threads
1,196,013
Messages
6,012,857
Members
441,736
Latest member
Tkpmm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top