Question About Paste and insert?

benpatz

New Member
Joined
May 21, 2015
Messages
9
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

fhqwgads

Board Regular
Joined
Jul 17, 2018
Messages
173
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
 

Watch MrExcel Video

Forum statistics

Threads
1,096,305
Messages
5,449,565
Members
405,571
Latest member
Flux1234

This Week's Hot Topics

Top