Profunds index and sector trading

[hk2]

OT-Excel coding issue

I take project data on a spreadsheet and filter it by department. Then I copy it to separate tabs (over 20) by department. (Then the department manager just clicks on HIS tab to see HIS task requirements)

Using the macro recorder, & some light editing, I pieced together a 10-page monster of code to do the job. I wanted to tighten the code & speed up the macro.

So I bought the "Excel 97 Programming for Dummies" book (cue Jaws music)

What DOES work, is this bit to look for data under the header, and if no data, then delete the sheet. It loops through all the sheets. This is part of the file clean-up at the end.

 
     For Each sht In ActiveWorkbook.Worksheets 
         If sht.Range("A2").Value = Empty Then sht.delete 
     Next sht 
  
 

==================================================

After adding 20 new sheets, I want to freeze the panes so the header info is always visible. Currently, I select the sheets one at a time and go through the steps

 
  
     sheets("xxx").Select 
     ActiveWindow.ScrollRow = 1 
     Range("A2").Select 
     ActiveWindow.FreezePanes = True 
 

I tried building on the looping code code above, and it DOES work on the FIRST sheet. But when it loops around, it fails on the second line of code

What does NOT work:

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

Error msg: Activate method of range class failed.

I have also tried the code below with the same result.

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

Any Ideas?

Thanks,