Replies to post #26268 on Profunds index and sector trading (PRO)

[Namiar]

This problem is a bit beyond my ability to fix it within the confines of this forum. I do not have an easy fix. This is a type of problem that I would need to see in front of me and I would probably need to set aside an afternoon to knock it out.

But I can tell you already that I am not entirely comfortable with some of those Excel VBA constructs that you are using. I would be more inclined to take a more crude "brute force" approach to fixing your problem, using logic/structures that I am more comfortable with.

BTW, my primary resource is Excel 2002 Visual Basic for Applications Step by Step. Probably has an updated version out by now.

[Dimension]

hk2: to operate on a given a sheet, it must have the focus.
so you need to add a "sht.Select" or "sht.Activate" before attempting to freeze the row

Change:

For Each sht In ActiveWorkbook.Worksheets 
     sht.Range("A2").Select 
     sht.Range("A2").Activate 
     ActiveWindow.FreezePanes = True 
 Next sht

To:

For Each sht In ActiveWorkbook.Worksheets 
     Sht.Select 'COMMENT: can also use Sht.Activate here 
     sht.Range("A2").Activate 
     ActiveWindow.FreezePanes = True 
 Next sht