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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
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
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
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
Try:

Code:
ActiveCell.EntireRow.Insert 
Worksheets("Sheet2"".Range("E2:L2").Copy Range("B" & ActiveCell.Row)
 
Upvote 0
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
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,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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
Back
Top