VBA cant paste into destination file without hitting enter

Jeevz_87

New Member
Joined
Sep 21, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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:

VBA 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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.
 
Upvote 0
You have. I'd suggest you try changing this:

Code:
Application.SendKeys ("~")

to this:

Code:
Application.Cutcopymode = false

and see if that helps.
 
Upvote 0
Solution
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!
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top