Excel vba paste from clipboard to another sheet only values

Question

I have a workbook i need to paste data to. What im trying to achieve:

Workbook i want to paste data to is active
Sheet in workbook i want to paste data to is not active
User activates another workbook that is already open selecting the entire sheet and copies it to the clipboard.
User goes back to the workbook data is going to be pasted to and runs a macro that pastes that data into another empty sheet with predefined sheet name.
Paste must paste values only, no formatting, no comments, shapes, no merged cells. Only the values.

And i have tried alot of combinations .Paste Destination[...] and so on.

What im using today is:

Dim wb As Workbook
Dim wsPT As Worksheet
Dim wsQD As Worksheet

Set wb = ActiveWorkbook
Set wsPT = wb.Sheets("PasteTemplate")
Set wsQD = wb.Sheets("QuoteData")

wsPT.Cells.Clear
wsPT.Range("A1").PasteSpecial xlPasteValues

Could you show us what you've tried already? So we can see if you've gone wrong with any of them or so we know what not to try, cheers. — Josh Whitfield

Vegard Vegard · Accepted Answer · 2016-01-27T09:32:54

The reason it fails is because the clipboard doesn't contain anything to be pasted, due to this line:

wsPT.Cells.Clear

When you clear the cells, you also clear the copy-command that was issued prior. Similarly, the copy-command will be cleared if you enter or modify information in another cell, or even if you just enter the cell itself.

I don't have a perfect workaround for your specific workflow, but if you are willing to modify step 4 so that the macro can be run from any sheet (specifically, the user must run the command from the same sheet they are copying from, either from the menu or by you placing the macro on the toolbar), this code should work:

Dim wb As Workbook
Dim wsPT As Worksheet
Dim wsQD As Worksheet
Dim pRng As Range

Set wb = ActiveWorkbook
Set wsPT = wb.Sheets("PasteTemplate")
Set wsQD = wb.Sheets("QuoteData")
Set pRng = Selection                   <~~ Store the range we want to copy

wsPT.Cells.Clear                       <~~ Clear the contents of the worksheet we are pasting to
pRng.Copy                              <~~ Copy the range
wsPT.Range("A1").PasteSpecial xlPasteValues

Run the macro from the sheet where data is being copied from after the desired range has been selected.

Excel vba paste from clipboard to another sheet only values

2 Answers