VBA cant paste into destination file without hitting enter

[Jeevz_87]

Hi All,

Im having an issue with a simple cut and paste vba code which I have used previously.

Im trying to copy from a range in one work book and paste values only into another. The problem Im having is, even after defining the workbook, sheet and cell location, Excel still asks me to hit enter before the latter part of the code can be executed.

Note: I have looked into using send keys to programmatically hit enter but it didnt work.

The code:

Sub Copy_Contract_Reviews()



Dim wb As Workbook
Set wb = Workbooks.Open("C:\Users\Jeevan.Ruprai\XXXX\XXXXXXXXXX - General\Working Folders\Jeevan's Working Folder\KPI\Dummy Test\Feb 22 - Post Contract Review.xlsx")

Workbooks("Feb 22 - Post Contract Review.xlsx").Worksheets("MAPS Database").Range("A19:HD3000").Copy
Workbooks("BLANK_Consolidated KPI data collection (AOR data source) .xlsm").Worksheets("Data Import").Range("A2").PasteSpecial Paste:=xlPasteValues
Application.SendKeys ("~")

ActiveWorkbook.RefreshAll
MsgBox "All tables refreshed!"


End Sub

Is there a way to get around this?

Cheers

 

[RoryA]

Excel still asks me to hit enter

What exactly does the message say?

 

[Jeevz_87]

Hi RoryA,

The message says "Select destination and press ENTER or choose Paste" however, I though I had already dealt with that within the code.

 

[RoryA]

You have. I'd suggest you try changing this:

Application.SendKeys ("~")

to this:

Application.Cutcopymode = false

and see if that helps.

 

[Jeevz_87]

Hi RoryA,

Happy to report that it did the job! The only thing that didnt happen was that the paste function didnt paste values only - it pasted the data exactly as it was in its raw form however I've compensated for this in power query

Thanks again!