Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3041
  • Last Modified:

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.
0
rcjax
Asked:
rcjax
  • 2
1 Solution
 
jsemenakCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now