Excel: copy and paste without using the clipboard

[jeeswg]

- Is there a way in Excel to copy and paste, without using the clipboard?
- I've made an attempt at copying cells from one sheet to another, below, with/without using the clipboard. But it's possible that the script could be improved.

Code: Select all

q:: ;Excel - copy and paste with/without the clipboard attempts
oXl := ComObjCreate("Excel.Application")
oXl.Visible := True
oXl.Workbooks.Add

;populate Sheet1 with cell with data
for oCell in oXl.Sheets("Sheet1").Range("A1:B2")
	oCell.Value := oCell.Address(0, 0)

;copy cells to Sheet2 via copy and paste
oXl.Sheets("Sheet1").Range("A1:B2").Copy
oXl.Sheets("Sheet2").Paste
oXl.Application.CutCopyMode := False

;attempt to copy cells to Sheet3 without using the clipboard
for oCell in oXl.Sheets("Sheet1").Range("A1:B2")
{
	oXl.Sheets("Sheet3").Range(oCell.Address).Formula := oCell.Formula
	oXl.Sheets("Sheet3").Range(oCell.Address).NumberFormat := oCell.NumberFormat
	oXl.Sheets("Sheet3").Range(oCell.Address).Interior.ColorIndex := oCell.Interior.ColorIndex ;highlight colour
}

oXl := ""
return

[FanaticGuru]

- Is there a way in Excel to copy and paste, without using the clipboard?
- I've made an attempt at copying cells from one sheet to another, below, with/without using the clipboard. But it's possible that the script could be improved.

Not that I have discovered.

You are only transferring 3 properties of a cell. That is like describing a person by just height, weight, and hair color. It is a good start but far from comprehensive.

There are many more properties that are copied with paste. Borders (sides, styles, colors, weights, etc), font (colors, styles, sizes, etc), interior (color, pattern, pattern color, etc), alignment (vertical, horizontal, indent, orientation, etc), conditional formatting, width, height, protected, the list goes on and on.

I have found it impractical to try to manually copy all the properties of one cell to another.

At one time I tried in VBA when I was attempting to make a function to swap two cells without copy/pasting to a temporary holding cell. I didn't start out transferring many properties but then as time went on I kept discovering more and more things that I needed transferred until it started being too slow when doing ranges and I gave up and took a different approach in my overall script so that I did not need a swap function.

FG