Recorded macro fails after save, close, then reopen

[Marvin Buzz]

I recorded the following macro

Sub Macro1()
'

'

'
Application.Goto Reference:="Print_Area"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
Columns("A:W").Select
Columns("A:W").EntireColumn.AutoFit
End Sub

PrintArea is a portion of a Pivot Table

The macro works fine as long as the workbook remains open. After I close
the workbook, and subsequently open it at a later time, the macro fails on
the statement

..LineStyle = xlContinuous

with a message that states

Runtime error 1004

Unable to set the line style of the border class.

Any help would be appreciated.

 

[Barb Reinhardt]

I'd be willing to bet that it's failing because you don't have an Inside
Vertical or Inside Horizontal in your print area.

Put
On Error Resume Next

at the beginning of your sub and

On Error GoTo 0

at the end of this sub.

 

[Marvin Buzz]

Barb-

Your solution works. Would you mind explaining what went wrong, and how you
came upon your solution?

Thanks.

 

[Don Guillett]

try

Sub borderprintarea()
With Range("Print_Area").Borders
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
Columns("A:W").EntireColumn.AutoFit
End Sub

 

[Marvin Buzz]

Don-

Barb's reply worked for me. I was curious as to why it was necessary to
ignore the error, why the error occurred, and what insight she had that led
to the resolution.

Thanks.

 

[dustinbrearton via OfficeKB.com]

All the error handler said was if you have a problem then skip the part you
are having problems with and goto the next step. Barb was saying that you
probably have not vertical or horizontal in your selection area. If you want
to test that then take the error handling parts that Barb had you put in out
and remove the below sections of code.


With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With

Don-

Barb's reply worked for me. I was curious as to why it was necessary to
ignore the error, why the error occurred, and what insight she had that led
to the resolution.

Thanks.

try

[quoted text clipped - 67 lines]

 

[Don Guillett]

What result did mine give?

 

[Barb Reinhardt]

It worked because you had no inside verticals or inside horizontals. I don't
know without looking at your selection.

I came across this solution because I've seen it before. I haven't tried
Don's solution, but I will the next time.

 

[Marvin Buzz]

To all-

Thanks for the responses. I did not go any further than Barb's suggestion.
For clarification purposes, I guess I was asking why it worked when I
recorded the macro, why it continued to work when I ran the macro, and why it
failed after the workbook was closed and subsequently opened.

Thanks again.

Marvin