How to write a VB script in Excel to copy from a cell range and paste to another while inserting a new row?

I am trying to write a VB script that I can assign to a button on a Excel workbook. What I need the script to do is copy a cell range (i.e., a10:l10) and paste it to another area in the same spreadsheet. I also need to clear the copied cells and cause the spreadsheet where I paste the data to automatically insert a new row.  This is for an Excel spreadsheet using Microsoft VB.
rcjaxAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
jsemenakConnect With a Mentor Commented:
Hi,

Sub Macro1()
    Range("A10:I10").Select
    Selection.Copy
    Range("A16").Select
    ActiveSheet.Paste
    Range("A10:I10").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Rows("16:16").Select
    Selection.Insert Shift:=xlDown
End Sub

This macro copy A10:I10 to A16 :I16 u can modify target.
0
 
dapcomCommented:
Try this: (done with the macro recorder)

    Range("A1:C1").Select
    Selection.Copy
    Range("A5").Select
    Selection.Insert Shift:=xlDown
    Range("A1:C1").Delete

This actually does not insert a whole row but move the cells down for the range with only.
It should more or less do what you seek?
0
 
dapcomCommented:
lol, 1 min too late :)
0
All Courses

From novice to tech pro — start learning today.