columns spontaneously hidden when file reopened

[dlh]

This has to be an Excel bug. Certain columns in my file are visible when the file is closed but are hidden when it's reopened in 2003. It doesn't happen in 2007, but does happen the first time the file is reopened in 2003.

More detail: I've got a "template" sheet with a group of visible columns and, immediately to their right, a group of hidden columns (D:G visible, H:K hidden). My macro copies this set of visible & hidden columns and pastes them multiple times as a template onto another sheet. The rightmost visible columns in all these template-pastes (column G in the template) are the ones that become spontaneously hidden upon reopening the document.

I've used a debug-watch on the ColumnWidth property and verified that my code is not changing anything either on save or reopen.

I do have a solution to my problem. If I manually change one of the problem columns' widths before saving, then that column doesn't disappear. If I set a single one's ColumnWidth via code to the value it already is, then all columns don't disappear. And the problem only occurs after my copy-paste macro has been run, so there's no danger of columns re-disappearing.

ActiveSheet.Columns("P").ColumnWidth = ActiveSheet.Columns("P").ColumnWidth

Can anyone tell me anything more about this bug? Is there any way to cleanse the file so that my workaround is not necessary?

(Unfortunately this is part of a fairly complicated VBA application, which is why I haven't posted the file. If necessary, I'll try to create a stripped down version that has the problem.)

[SamT]

Try Insert instead of Paste.

[dlh]

Interesting thought, but the result with Insert is the same as Paste.

And the problem is not dependent on my macro; I recreated the symptom doing only manual editing.

[lucas]

I don't think it's a bug. Something you are doing is causing it.

[SamT]

pastes them multiple times as a template onto another sheet.

Show us that part of your code, please.

[dlh]

Attached is an extremely stripped down version of my file that still has the same problem, without any VBA code.

To recreated the problem do the following manually using Excel 2003: First copy columns "M:U" from the "template" sheet. Then insert them before column "M" on the "report" sheet. Then save the file. Notice that column "P" has width of 8.43. Close the file and reopen. Notice that column "P" is now hidden and has width 0.

Surely this is a bug. It doesn't happen in Excel 2007.

Just in case it's relevant, below is the code I use to copy/paste. I don't think the source of the problem is here though:

 
'variables CompanyName & PrevCompanyCount defined elsewhere
Dim ReportSheet As Worksheet
Dim CopyTo as Range
Dim CopyFrom as Range
 
'identify company-report page
Set ReportSheet = ThisWorkbook.Worksheets("report")
ReportSheet.Unprotect
With ThisWorkbook.Names("template").RefersToRange
    'identify rightmost edge of table
    Set CopyTo = ReportSheet.Columns(4 + .Columns.Count * PrevCompanyCount)
    'insert space
    CopyTo.EntireColumn.Resize(ColumnSize:=.Columns.Count).Insert
    'shift back into newly inserted space
    Set CopyTo = CopyTo.Offset(0, -.Columns.Count)
    'calc which of 3 colored-templates to use (mod 3)
    Set CopyFrom = .Offset(ColumnOffset:=.Columns.Count * (PrevCompanyCount Mod 3))
End With
'copy/paste template
CopyFrom.Copy Destination:=CopyTo
'fill in CompanyName
CopyTo.Cells(5, 1).Value = CompanyName

[dlh]

Please forgive this reply to my own message. I'd still be interested in any feedback regarding the "bug" I've described.

[mdmackillop]

I see that it is happening. At a loss as to why, though.
Regards
MD