CODEWITHSUNDEEP
vbaexcelexcel-2003
Seehyung Lee
Seehyung Lee

Reputation: 610

Macro - Copy and Paste

My company only uses MS Office 2003 products so I have to stick to it. Because of the nature of my job, i need to use a lot of "copy and paste" function. The source data is mostly from the website and i paste the data into a cell in Excel. The problem is clipboard keeps source formatting and it reflects on the cell when i paste it. That is really troublesome to remove the source format by selecting option "Paste as Text" every time i user copy and paste. SO i decided to use Macro. The Macro works perfectly when i try to copy and paste from website to excel, but when i copy and paste from Excel to the same work Sheet it throws an error.

This is the code i use for copy and paste from website to excel without source formatting.

Sub Paste_without_any_formatting()
    ActiveSheet.PasteSpecial Format:="Text"
End Sub

I want to add another code for copy and paste from excel to the same work Sheet like this.

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

How can i put these two codes together? I want to make these happen magically when i press Ctrl+V. Can anyone help me?

Upvotes: 2

Views: 2647

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

This is the most simplest way to achieve it.

Sub Sample()
    On Error GoTo Whoa1

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Exit Sub
Pastetext:
    On Error GoTo Whoa2

    ActiveSheet.PasteSpecial Format:="Text"

    Exit Sub
Whoa1:
    Resume Pastetext
Whoa2: '<~~ If both Paste method fails then show message. Ex: Image in Clipboard
    MsgBox err.Description
End Sub

There is one more way where you try to ascertain the clipboard data type and then paste accordingly.

Upvotes: 2

Related Questions