Application.Paste confusion

[JWDeiley]

Good day.

Just for fun I've been programming a game in Excel VBA. I've been working on this for most of this year. I am using MSoffice 2021.

Suddenly, code that has literally worked for 10 months now just plain stopped working. Here is the code:

' Got to the game pictures page and get the init pic (World Gate)
Sheets("GamPic").Select
For Each MyShp In ActiveSheet.Shapes
If MyShp.Name = MyshpName Then
MyShp.Select
Selection.Copy
Sheets("Interface").Select
ActiveSheet.Range("J5").Select
ActiveSheet.Paste
ActiveSheet.Range("T29").Select
Exit For
End If
Next MyShp

The code crashes at the ActiveSheet.Past line. It says "Paste method of worksheet class failed"

I'm thinking that something has changed in Excel. If I run the code under Excel 2007 it runs flawlessly. I've tried other things such as selecting the cell J5 and then manually pressing cntrl v and it will paste in the shape (an image). So I took out the activesheet.paste and used SendKeys("^v") and it will not work unless I change the Exit For command to Exit Sub.

Any ideas on what I've done wrong and how to fix it?

Thank you for your help

JD

 

[NdNoviceHlp]

Hi JW. I trialed your code as follows with XL 21 with a test pic and it worked fine.

Dim MyshpName As String
MyshpName = "Picture 1"

Sheets("GamPic").Select
For Each MyShp In ActiveSheet.Shapes
If MyShp.Name = MyshpName Then
MyShp.Select
Selection.Copy
Sheets("Interface").Select
ActiveSheet.Range("J5").Select
ActiveSheet.Paste
ActiveSheet.Range("T29").Select
Exit For
End If
Next MyShp

Perhaps your use of Active sheet is causing problems. Also, try to code without the use of Selection as it slows code down. Here's the same code that avoids the use of active sheet and selection (mostly). It also test OK. HTH. Dave

Dim MyshpName As String
MyshpName = "Picture 1"

For Each MyShp In Sheets("GamPic").Shapes
If MyShp.Name = MyshpName Then
MyShp.Copy
Sheets("Interface").Paste
Application.CutCopyMode = False
With Sheets("Interface").Shapes(Sheets("Interface").Shapes.Count)
    .Top = Sheets("Interface").Range("J5").Top
    .Left = Sheets("Interface").Range("J5").Left
End With
Sheets("Interface").Activate
Sheets("Interface").Range("T29").Select
Exit For
End If
Next MyShp

ps. please use code tags

 

[JWDeiley]

Hi JW. I trialed your code as follows with XL 21 with a test pic and it worked fine.

Dim MyshpName As String
MyshpName = "Picture 1"

Sheets("GamPic").Select
For Each MyShp In ActiveSheet.Shapes
If MyShp.Name = MyshpName Then
MyShp.Select
Selection.Copy
Sheets("Interface").Select
ActiveSheet.Range("J5").Select
ActiveSheet.Paste
ActiveSheet.Range("T29").Select
Exit For
End If
Next MyShp

Perhaps your use of Active sheet is causing problems. Also, try to code without the use of Selection as it slows code down. Here's the same code that avoids the use of active sheet and selection (mostly). It also test OK. HTH. Dave

Dim MyshpName As String
MyshpName = "Picture 1"

For Each MyShp In Sheets("GamPic").Shapes
If MyShp.Name = MyshpName Then
MyShp.Copy
Sheets("Interface").Paste
Application.CutCopyMode = False
With Sheets("Interface").Shapes(Sheets("Interface").Shapes.Count)
    .Top = Sheets("Interface").Range("J5").Top
    .Left = Sheets("Interface").Range("J5").Left
End With
Sheets("Interface").Activate
Sheets("Interface").Range("T29").Select
Exit For
End If
Next MyShp

ps. please use code tags

Wow. That solved the problem. I'm new to VBA programming and I obviously have much to learn. Thank you so much for your help!

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave

 

[JWDeiley]

Ok, I just wanted to post an update to this issue in case others have the same issue.

All my troubles started after Windows 11 did an update. After the update it placed an icon on my taskbar for a program called Copilot. I looked in my installed software list and Copilot is not listed. However, I did notice that my MS Office 2021 is now shown as Office 365 2021. Copilot is an AI assistant that is included with Office 365. I went into my MS account and took a look at my subscriptions. It states that I own MS Office 2021.

So, I uninstalled Office and then had it reinstalled from my MS account. Looking at the installed software it again says I have Office 365 2021, Copilot, snd my Excel VBA still has issues.

My next step (according to the internet) is to disable Copilot using the Group Policy Editor in Windows 11. I don't know why this would effect VBA code... but I'm running out of answers. This weekend I will do that and report if it fixes this issue. If it does, then all Windows 11 users with MS Office need to take care if the same update is performed on your system. Who knows what else this update can effect...

Thank you for all your help. It is appreciate.