Insert into ActiveCell?

[Bmeyer]

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

 

[Peter_SSs]

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"

 

[Yard]

Maybe just:

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

 

[Bmeyer]

Thanks

Peter your code works fine!

Yard thanks bud

 

[Peter_SSs]

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.

 

[Bmeyer]

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

 

[Peter_SSs]

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>

 

[Bmeyer]

Perfect

Thanks