excel template filter moves in created document created from template

Hello everyone,

Thanks for helping to make life easier!

I have a problem that's been bugging me for a while now, not a critical one but annoying nevertheless. I have a template (.xlt) set up for generating plant schedules that we use on our landscape planting plans. The excel file is basically a categorized list of all the plants we typically use, but for each project we only use a small selection of those. A column at the far right of the schedule shows the total numbers of each plant.

There are two macros in the template, one to filter on the Totals column to hide all rows without anything in the Totals column, the other to show all rows again.

The macros are basically this:

Sub PlantList1()
'
' PlantList1 Macro
' Reduce Plant List
'

'
    Selection.AutoFilter Field:=1, Criteria1:=">0", Operator:=xlOr, _
        Criteria2:="=***"
End Sub
Sub TotalPlantList1()
'
' TotalPlantList1 Macro
' Show Total Plant List
'

'
    Selection.AutoFilter Field:=1
End Sub

Select allOpen in new window

This is all fine, and the first few rows of the template file look like this:
plantschedule2012-08-22-143944.pdf

As you can see, there is a filter in column G, which has a list range of $G$1:$G$870

Now, take a look at what happens when a new document is generated from the template:
plantscheduleB-2012-08-22-144132.pdf

The filter range has changed from the template, to this: $A$2:$G$867. The macros still work fine, but I would like the filtering to remain as it is in the template, because the filter arrows start to obscure the text in row 2, and because of the way we use the excel file in the final product, these arrows all still show. I would like to make some of the columns narrower but I can't because otherwise the text in those columns would not be able to be read properly.

So, can anyone tell me why this happens between the template and the new document? It's not specific to the version of Excel either, we have both 2003 and 2007 and the behaviour is the same.

Look forward to your replies.

Richard