Copy to clipboard

[andyb63gb]

Hello all, sorry if this has already been covered

I simply want a macro in excel that will do the following;

copy the contents of cell A1 to windows clipboard (to paste into another program using Ctrl V)
delete cell A1 moving column up
select cell A! for next time.

Ive tried;

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+z
'
Range("A1").Copy
ActiveCell.FormulaR1C1 = ""
Range("A1").Select
Selection.Delete Shift:=xlUp
End Sub

but when I go tho the program field I want to copy to, its not there.

Any tips please

 

[CalcSux78]

Welcome to the forum!

There are quite a few ways to load the clipboard. I think you're method is failing because when you use Copy, you'll notice a dotted line around the range being copied. Once the dotted line disappears, windows drops the item in the clipboard.

Try:

Sub LoadClipboard()
Dim dObj As Object


Set dObj = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
dObj.SetText [A1]
dObj.PutInClipboard
Set dObj = Nothing


[A1].Delete Shift:=xlUp


End Sub

 

[BarryL]

alternative way using Microsoft Forms reference

Sub Macro1()
Dim clipb As MSForms.DataObject
Set clipb = New MSForms.DataObject
clipb.SetText Range("A1").Value
clipb.PutInClipboard
End Sub

 

[CalcSux78]

Good one, Barry... I use your version too. In order for it to work, you'd either have to insert a userform into your VBA Project or add a reference to Microsoft Forms 2.0 Object Library.

Andy, here's an article to catch you up: Copy and Paste in VBA | Byte Comb

 

[andyb63gb]

Thanks Calcsux78, that did the trick.