CODEWITHSUNDEEP
vbaexcelpaste
OyvPet
OyvPet

Reputation: 107

Excel vba paste from clipboard to another sheet only values

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

  1. Workbook i want to paste data to is active
  2. Sheet in workbook i want to paste data to is not active
  3. User activates another workbook that is already open selecting the entire sheet and copies it to the clipboard.
  4. 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.
  5. 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

Upvotes: 0

Views: 8729

Answers (2)

Vegard
Vegard

Reputation: 4952

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.

Upvotes: 2

Josh Whitfield
Josh Whitfield

Reputation: 321

Have you tried..?

Range("A1").Copy Destination:=Workbooks("QuoteDate").Sheets("PasteTemplate").Range("A1")

This copies from the quote Date sheet and pastes in the Paste template sheet, swap those names if you want it the other way around.

Upvotes: 0

Related Questions