Results 1 to 2 of 2

Thread: Question About Paste and insert?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2015
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Question About Paste and insert?

    I have two sets of code, The first one, copies and inserts the copied range on to a different sheet and the Second, copies and pastes the range on to a different sheet. There is one line of code that has to be different for me. I put the lines in question in bold. Why is this? Does it have to do something with VBA objects? I am fairly new and was wondering why such a small change makes programming seem more frustrating than it often appears. Is there any easier way write either of these codes?

    Sub CopyandInsert()
    'This code copies and inserts via shift cells down.
    ThisWorkbook.Worksheets("Sheet1").Select
    ActiveSheet.Range("A2:D5").Select
    Selection.Copy
    ThisWorkbook.Worksheets("Sheet2").Select
    ActiveSheet.Range("A12:D15").Select
    Selection.Insert Shift:=xlDown


    Columns("D:D").NumberFormat = "$0.00"
    End Sub

    Sub CandP()
    'This code copies and pastes
    ThisWorkbook.Worksheets("Sheet1").Select
    ActiveSheet.Range("A2:D4").Select
    Selection.Copy
    ThisWorkbook.Worksheets("Sheet2").Select
    ActiveSheet.Range("A4:D6").Select
    ActiveSheet.Paste

    Columns("D:D").NumberFormat = "$0.00"
    End Sub

  2. #2
    Board Regular
    Join Date
    Jul 2018
    Posts
    163
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Question About Paste and insert?

    firstly i think you can shorten some of your code so instead of

    Code:
    ThisWorkbook.Worksheets("Sheet1").Select
    ActiveSheet.Range("A2:D4").Select
    Selection.Copy
    you can just have

    Code:
    ThisWorkbook.Worksheets("Sheet1").ActiveSheet.Range("A2:D4").Copy
    but i duno maybe you just prefer your way

    as for your other question, i dont know much about VBA yet and dont understand objects and such too well, but i think it's just how paste and insert work differently. when you copy a cell and right click paste it just does it but when you right click insert a copied cell it has to ask you whether you want to do it down or right so you need that extra Shift:=xlDown bit in the code

    hope that helped. sorry if it didnt

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •