CopyOrigin

Ilan Sebba

unread,

Mar 28, 2002, 7:23:44 AM3/28/02

to

Does anyone know how to use the "CopyOrigin" in the Range.Insert method?
Could anyone post us an example? I found nothing in Google. Thanks

I reproduce below what Excel VBA help says about CopOrigin (it says that it
is the "copy origin", - I am none the wiser after that explanation).

Thanks

Ilan

Excel VBA Help:
"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.
...
CopyOrigin Optional Variant. The copy origin."

Bob Flanagan

unread,

Mar 28, 2002, 7:55:30 AM3/28/02

to

Ilan, the Excel 97 VBA help on the Insert function does not mention any
copyorigin. I've copy the Excel 97 help below for your review. I use this
approach in Excel 97-XP without any problems.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

Syntax 1 (Range object): Inserts a cell or a range of cells into the

worksheet or macro sheet and shifts other cells away to make space.

Syntax 2 (Characters object): Inserts a string preceding the selected
characters.

Syntax 1

expression.Insert(Shift)

Syntax 2

expression.Insert(String)

expression Required. An expression that returns a Characters or 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.

String Required String. The string to insert.

"Ilan Sebba" <ilan....@btinternet.com> wrote in message
news:a7v20f$qb1$1...@helle.btinternet.com...

Ilan Sebba

unread,

Mar 28, 2002, 6:45:11 PM3/28/02

to

:-)

Yes, I manage to use the Range.Insert with the Shift parameter. I posted my
question not because I have a problem, but because I am curious.

In Excel 2002 (and possbilty 2000 as well) there is an additional optional
paramater , "CopyOrigin". There is some (useless) help about this in the
documentation. I cannot figure out what this option does. I think (just
guessing) that it is a parameter which may be used to insert values into the
newly created range.

So, if you can figure out what the "CopyOrigin" parameter does in 2002 (and
how to use it), you will satisfy my curiousity.

Thanks

Ilan

"Bob Flanagan" <nor...@noreply.net> wrote in message
news:eqbEOil1BHA.1360@tkmsftngp05...

Dave Peterson

unread,

Mar 28, 2002, 8:32:29 PM3/28/02

to

I have no answer, but it almost looks to me like it defines what it's pasting.
Kind of a what's in the clipboard.

If you look at the help for clipboard, you'll see a lot of clipboardformats:
xlClipboardFormatBIFF
xlClipboardFormatBIFF2
xlClipboardFormatBIFF3
xlClipboardFormatBIFF4
xlClipboardFormatBinary
xlClipboardFormatBitmap
xlClipboardFormatCGM
xlClipboardFormatCSV
xlClipboardFormatDIF
xlClipboardFormatDspText
xlClipboardFormatEmbeddedObject
xlClipboardFormatEmbedSource

etc, etc, etc....

I tried putting different things in the clipboard (manually via just Edit|copy
in notepad and other programs) and seeing what happens when I tried:

Columns("F:F").Insert Shift:=xlToRight, copyorigin:=xlClipboardFormatBitmap
or
Columns("F:F").Insert Shift:=xlToRight, copyorigin:=xlClipboardFormatText

But I didn't see any difference between anything.

====
Even though the help said Variant, it didn't like ranges or text. It didn't
blow up with true/false/Longs.

So my answer is: Maybe someone else can explain!

--

Dave Peterson
ec3...@msn.com