Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Paste Clipboard Content to Excel Sheet Using VBA

Have you ever needed to paste clipboard content to an excel sheet. Content in the clipboard may have copied from a web page, software, word file or text file etc. And content may be in any form.  It may be a text, table, image and so forth. So in this post I will teach you how to do this using VBA.

Method 1

In this method, first you need to add reference to Microsoft Forms 2.0 Object Library. To do that go to VBA editor and click Tools menu and then click on References.

Then put a tick to Microsoft Forms 2.0 Object Library. However you will notice that Excel application automatically add reference to that library if you add form to your project.

Then add this code to a module. In this code, clipboard content is assigned to the SText variable. So at the end you can paste it to the place where you need. In this example content is pasted to the B2 cell of the activesheet.

Sub PasteToExcelFromClipboard()

Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject

SText = DataObj.GetText(1)

ActiveSheet.Range("B2").Value = SText

End Sub

However this method will not work if you have an image in the clipboard. So if you want to deal with images then you can use this second method.

Method 2

Actually in this method we are using a very simple theory. We know that Ctrl + v is the shortcut keys to paste anything to the Excel sheet. So instead of doing this manually we can give that command through VBA like this.

Sub PasteToExcelFromClipboard_SendKeyMethod()

activesheet. Range("B2").Select
SendKeys "^v"

End Sub

First we select the B2 cell of the active sheet. Then we give the paste command using SendKeys method.  Using this method you can even paste images, shapes and tables from the clipboard too.

Contact Form


Email *

Message *

Popular Posts