0

I am newly encountering this error. I am not sure where it is coming form. I have run this bit of code literally thousands of times and now today for some stupid reason it is failing. I have been looking at this code and I it has not changed in months in addition to this still is the identical code that the VBA Recording tool generates. This is the Error message: Error Message

and this is the bit of code that my program is getting stuck on:

Sheets("Sheet1").Select
Cells(wHeaders, 1).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Workbooks(CurrWB).Activate
Sheets(CurrSheet).Select
Sheets(CurrSheet).Activate

Cells(CurrRow, CurrCol).Select
ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
    False, NoHTMLFormatting:=True     'THIS IS THE LINE WITH THE ERROR

ActiveSheet.Paste
Selection.EntireColumn.AutoFit

Because there have been no changes and that the code builder is still creating this same line I am very confused. Help would be amazing. Also because I know that you are all going to ask, yes my variables are defined. 

Dim CurrSheet As Variant
Dim CurrWB As Variant
Dim CurrCol As Variant
Dim CurrRow As Variant

There is one last one and that is passed in the Sub and that is Sub CoolProg(Optional wHeaders As Integer = 1)

UPDATE: I am not sure why this is the case but the error seems to go away when I make the following edit to the line where the debugger goes. 

Worksheets(CurrSheet).PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
    False ', NoHTMLFormatting:=True

All that I did was comment out the NoHTMLFormatting section, can someone help me understand why this works and maybe what is the underlying cause of this error. I want to make sure that I understand this so that I can fix what code I need to.

Update #2 .... So this error only happens when I have a Google Chrome window open and I am running this code. I closed Chrome and it worked. There was a similar task that relied on this but I know to be VERY Compute heavy and it uses this sub within it and I couldn't get through it. I checked task manager and saw that chrome was at the top (PS it SUCKS resources) so I killed and and now it works. If someone can explain what is going on that would be amazing.

Update#3

I added a 2 second pause before the problematic line runs and it seems to solve the issue. I am not sure why this works but it seems that there is a larger resource draw and my machines needs the time for some of the resources to free up so that it will work. If someone has some insight as to why this is a solution that works that would be great. The code now looks like:

Cells(CurrRow, CurrCol).Select

Application.Wait (Now + TimeValue("0:00:02")) 'Added this line for the delay
ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
    False, NoHTMLFormatting:=True 'this is the problem code
Ben
  • 221
  • 1
  • 6
  • 20
  • Implicit late binding: `ActiveSheet` yields an `Object`. Try working with a `Worksheet` object instead. – Mathieu Guindon Oct 18 '19 at 21:24
  • `As Variant` is just more late binding. `CurrSheet` wants to be a `Worksheet`, `CurrWB` wants to be a `Workbook`, `CurrCol` and `CurrRow` want to be `Long` integers. – Mathieu Guindon Oct 18 '19 at 21:25
  • Also: [avoid Select and Activate](https://stackoverflow.com/q/10714251/1188513) – Mathieu Guindon Oct 18 '19 at 21:25
  • @MathieuGuindon I tired `Worksheets(CurrSheet).PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True` and I get the same error in the same spot. Also why did this suddenly stop working? – Ben Oct 18 '19 at 21:47
  • 2
    Have you tried `Range.PasteSpecial` instead, so that you don't need to select any cells and activate any sheets? Select and Activate are almost never needed, and they're an extremely error-prone way to do things. – Mathieu Guindon Oct 18 '19 at 23:01
  • Can you make clearer the relationship between between the two updates. The error goes away if you do either of those two things? – QHarr Oct 19 '19 at 06:08
  • It seems to be that when I do either of these two things that the error message disappears. I have been noticing that it seems to be more of an issue when I am running more programs. I am not sure why this is the issue, this error is not an every time error and instead it seems to happen sometimes and then others times it does not happen. Also it seems that I can hit debug it will go to this line then I can hit the continue button and it will execute without issues. Again this non repeatable behavior is really perplexing me with this error. – Ben Oct 21 '19 at 00:59
  • @MathieuGuindon `Range.PasteSpecial` produces a "Compile error: Argument not Optional" error. In this case I want to select the active sheet as the whole point is to copy in the sheet that was just downloaded. This is specifically a tool that allows the user to click it and then it will import the downloaded file into the sheet that they are working in. – Ben Oct 21 '19 at 01:05

0 Answers0