Excel vba paste from clipboard to another sheet only values -

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

1. workbook want paste data active
2. sheet in workbook want paste data not active
3. user activates workbook open selecting entire sheet , copies clipboard.
4. user goes workbook data going pasted , runs macro pastes data empty sheet predefined sheet name.
5. paste must paste values only, no formatting, no comments, shapes, no merged cells. values.

and have tried alot of combinations .paste destination[...] , on.

what im using today is:

dim wb workbook dim wspt worksheet dim wsqd worksheet  set wb = activeworkbook set wspt = wb.sheets("pastetemplate") set wsqd = wb.sheets("quotedata")  wspt.cells.clear wspt.range("a1").pastespecial xlpastevalues 

the reason fails because clipboard doesn't contain pasted, due line:

wspt.cells.clear 

when clear cells, clear copy-command issued prior. similarly, copy-command cleared if enter or modify information in cell, or if enter cell itself.

i don't have perfect workaround specific workflow, if willing modify step 4 macro can run sheet (specifically, user must run command same sheet copying from, either menu or placing macro on toolbar), code should work:

dim wb workbook dim wspt worksheet dim wsqd worksheet dim prng range  set wb = activeworkbook set wspt = wb.sheets("pastetemplate") set wsqd = wb.sheets("quotedata") set prng = selection                   <~~ store range want copy  wspt.cells.clear                       <~~ clear contents of worksheet pasting prng.copy                              <~~ copy range wspt.range("a1").pastespecial xlpastevalues 

run macro sheet data being copied after desired range has been selected.