Insert into ActiveCell?

Bmeyer

New Member
Joined
Mar 18, 2009
Messages
6
Hello All

I have a small problem here, I am new to VBA as you will see. The Following is my code I have written to copy a range from a Sample1 sheet and insert into the active sheet which is assigned as a variable. But when I run the macro it inserts the range into "A1"

I would like it to insert on the active sheet, in the active cell?

Sub Block()
'
' Block Macro
' Macro recorded 2009-03-20 by User
'
' Keyboard Shortcut: Ctrl+d
'
Range(ActiveCell.Address).Name = "StartCell"
Application.Goto "StartCell"
'Assign variable to active worksheet
Var1 = ActiveSheet.Name
'Insert three new rows
Sheets(Var1).Range("A1:E3").Insert Shift:=xlDown
'Copy Range from Sample worksheet
Sheets("Sample1").Range("A1:E4").copy Sheets(Var1).Range("A1")
Application.Goto "StartCell"
End Sub

Bmeyer
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
59,294
Office Version
  1. 365
Platform
  1. Windows
I haven't tested the code, but you told it to paste in cell A1 with this line:
Sheets("Sample1").Range("A1:E4").copy Sheets(Var1).Range("A1")

Try changing the "A1" in that line to "StartCell"
 
Upvote 0

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
Maybe just:

Code:
Sub Block()
' Keyboard Shortcut: Ctrl+d

'Insert three new rows
ActiveSheet.Range("A1:E3").Insert Shift:=xlDown
'Copy Range from Sample worksheet
Sheets("Sample1").Range("A1:E4").copy ActiveCell
End Sub
<!-- / message -->
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
59,294
Office Version
  1. 365
Platform
  1. Windows
Thanks

Peter your code works fine!
Well, it was actually your code - I just corrected it.


Yard thanks bud
Did you try Yard's code? Again, I didn't test it, but looks like it might do the same job much more efficiently. :)
 
Upvote 0

Bmeyer

New Member
Joined
Mar 18, 2009
Messages
6
Hi sorry one more??

I actually would like to insert the copied range into the active cell, as if I would want to add this between 2 ranges it wouldn't work, and it should move the rows down?


Sub Block()
'
' Block Macro
' Macro recorded 2009-03-20 by User
'
' Keyboard Shortcut: Ctrl+d
'
Range(ActiveCell.Address).Name = "StartCell"
Application.Goto "StartCell"
'Assign variable to active worksheet
Var1 = ActiveSheet.Name
'Copy Range from Sample worksheet
Sheets("Sample1").Range("A1:E4").copy Sheets(Var1).Range("StartCell")
Application.Goto "StartCell"
End Sub

Barry
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
59,294
Office Version
  1. 365
Platform
  1. Windows
Hi sorry one more??

I actually would like to insert the copied range into the active cell, as if I would want to add this between 2 ranges it wouldn't work, and it should move the rows down?
That isn't all that clear to me, but try this:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Block()<br>    Sheets("Sample1").Range("A1:E4").Copy<br>    ActiveCell.Insert Shift:=xlDown<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0

Forum statistics

Threads
1,190,896
Messages
5,983,441
Members
439,843
Latest member
PlanetFitness

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