VBA Code Works Only in debug Mode.

**princ_wns** · 05-28-2012, 09:46 AM

Hi All,

Today I came across a very intresting problem ie I wrote a line of code for sorting my data on the worksheet but line of code is not working Automatically.It is working only when i debug the same line. I dont know what is going on and why it is happening.

My Line of code is :

Code:

ThisWorkbook.Worksheets("TempCoverage").Range("A1").CurrentRegion.Sort Range("A2"), xlAscending, , , , , , xlYes

Thanks.

**Rick Rothstein** · 05-28-2012, 10:21 AM

I think your problem is revolving around your worksheet references... your sort range is referencing your TempCoverage worksheet whereas your Key1 argument is referencing the active sheet... if those two worksheets are the same, then the code works fine, but if the active sheet is not TempCoverage, then Excel generates an error (if you don't see an error message, it might be hidden behind an On Error trap... you didn't post enough code for us to know for sure or not). Try changing your posted line of code to this and I think it should work fine afterwards....

Code:

ThisWorkbook.Worksheets("TempCoverage").Range("A1").CurrentRegion.Sort ThisWorkbook.Worksheets("TempCoverage").Range("A2"), xlAscending, , , , , , xlYes

Although if you have other code that needs to reference the same worksheet within your procedure, it might be better to wrap everything using a With/End With block...

Code:

With ThisWorkbook.Worksheets("TempCoverage")
  .Range("A1").CurrentRegion.Sort .Range("A2"), xlAscending, , , , , , xlYes
'
'  other code needing to reference the same worksheet (note the leading dot in case With/End With is new to you)
'
End With

EDIT NOTE
---------------------
By the way, I am guessing your code worked in debug mode because you went to the TempCoverage worksheet "to see what was wrong" thus making it the active worksheet.