CopyOrigin on Insert in Excel VBA

Question

Can anyone tell me what the CopyOrigin parameter of Insert is used for? And what values it will accept?

I have included the vba help (which wasn't really that helpful):

Inserts a cell or a range of cells into the worksheet or macro sheet and shifts other cells away to make space.

expression.Insert(Shift, CopyOrigin) expression Required. An expression that returns a Range object.

Shift Optional Variant. Specifies which way to shift the cells. Can be one of the following XlInsertShiftDirection constants: xlShiftToRight or xlShiftDown. If this argument is omitted, Microsoft Excel decides based on the shape of the range.

CopyOrigin Optional Variant. The copy origin.

lakshmanaraj · Accepted Answer · 2009-03-26 04:55:17Z

26

It takes either of one parameter as given below.

Const xlFormatFromLeftOrAbove = 0

Member of Excel.XlInsertFormatOrigin

and...

Const xlFormatFromRightOrBelow = 1

Member of Excel.XlInsertFormatOrigin

edited Mar 26, 2009 at 4:55

answered Mar 26, 2009 at 4:43

lakshmanaraj

4,19525 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ZygD · Accepted Answer · 2015-08-31 20:48:59Z

23

Adding to Lakshmanaraj's comments - it picks up the formatting option depending on where you are inserting cells & what formatting you wish to pick.

Lets say you have:
first row which has bold text,
second row has things in italic.
You select the 2nd row & execute the following expression:

Selection.Insert CopyOrigin:=xlFormatFromLeftOrAbove

The new row gets inserted between 1st and 2nd row & it picks formatting rules from the "row above" or "cells to the left of the cell".

In this case, the newly inserted cells will have text as bold without you setting it explicitly.

edited Aug 31, 2015 at 20:48

ZygD

24.8k41 gold badges106 silver badges144 bronze badges

answered Mar 26, 2009 at 6:25

shahkalpesh

33.6k4 gold badges66 silver badges89 bronze badges

Comments

animuson · Accepted Answer · 2011-12-03 04:39:33Z

-2

You can Reference here :

Imports Excel = Microsoft.Office.Interop.Excel
Dim XLApp As New Excel.Application()
Dim xWkBook As Excel.Workbook = XLApp.Workbooks.Open(YourInitialPath)
Dim xSheet As Excel.Worksheet = CType(xWkBook.Sheets(1), Excel.Worksheet)

CurCell = xSheet.Range("G9:G11")
CurCell.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, CurCell.Copy())

edited Dec 3, 2011 at 4:39

animuson♦

54.9k28 gold badges142 silver badges150 bronze badges

answered Apr 18, 2011 at 4:49

user712793

292 bronze badges

3 Comments

panda-34 Over a year ago

This answer is absolutely misleading, there's no reason to put CurCell.Copy() inside Insert call as it doesn't even return anything but copies CurCell contents to clipboard later to be inserted by CurCell.Insert()

Patrick Lepelletier Over a year ago

curcell is what ? range variable ? planet variable ? Name of your wife ?

Patrick Lepelletier Over a year ago

this is not vba, you can't set a variable while using DIM. everything is 'syntax error' color flagged in VBE (exept the line with range).

Collectives™ on Stack Overflow

CopyOrigin on Insert in Excel VBA

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related