VBA PasteSpecial method Worksheet class failed

lionginass

New Member
Joined
Jul 29, 2016
Messages
19
Hello everyone,

I have a code which starts this:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Private Sub CommandButton1_Click()
Dim destination As Worksheet
Set destination = Worksheets("STATS")
Set myIE = CreateObject("InternetExplorer.Application")
myIE.Navigate "https://www.internetpage.com/lblabla.html"
myIE.Visible = True
Application.Wait (Now + TimeSerial(0, 0, 10))
SendKeys "^a"
SendKeys "^c"
Application.Wait (Now + TimeSerial(0, 0, 3))
destination.Activate
destination.DrawingObjects.Select
Selection.Delete
destination.Cells.Clear
destination.Range("A1").Select
ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
False, NoHTMLFormatting:=True
On Error Resume Next
myIE.Quit
Set myIE = Nothing
Err.Clear[/FONT]

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
When i click the command button i get an error "PasteSpecial method Worksheet class failed

Debbuger marks yellow this part:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
False, NoHTMLFormatting:=True

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Strange thing is when i run this code directly from the developer it runs without errors.
Who has the idea why clicking the button doesnt work here?

One more thing. The code turns off numlock key? Any ideas why?

Thanks[/FONT][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

[/FONT]


<strike>
</strike>
[/FONT]
 

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,524
Hi Lionginass,

next time when you post code please use cod brackets like shown in red & blue below.

As to your question:
What type of information is being pasted? What do you mean with "running the code directly from the developer"
 

Forum statistics

Threads
1,078,145
Messages
5,338,504
Members
399,238
Latest member
amuthan10

Some videos you may like

This Week's Hot Topics

Top