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.

Answer 1

It takes either of one parameter as given below.

Const xlFormatFromLeftOrAbove = 0

Member of Excel.XlInsertFormatOrigin

and...

Const xlFormatFromRightOrBelow = 1

Member of Excel.XlInsertFormatOrigin

Answer 2

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.

Answer 3

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())