pre-Formatted auto-generated Excel sheet

[jamesftoland]

Hello,

I have a macro that generates table information form Word into an excel
sheet, and I'd like it if the cells columns would text wrap the data in
the cells, as well as display the regular "medium" black Excel
gridlines....not sure where to introduce this code, as .Columns or
..Rows? Anyway, here is what I have that needs to be tweaked...Thanks
again all, I'm learning....

Next


.Columns("A:A").EntireColumn.AutoFit
.Columns("A:A").WrapText = True

.Columns("B:B").EntireColumn.AutoFit
.Columns("B:B").WrapText = True

.Columns("C:C").EntireColumn.AutoFit
.Columns("C:C").WrapText = True

Cell.Borders (xlEdgeLeft)
Border.LineStyle = xlContinuous
Border.Weight = xlThin
Border.ColorIndex = xlAutomatic
Cell.Borders (xlEdgeTop)
Border.LineStyle = xlContinuous
Border.Weight = xlThin
Border.ColorIndex = xlAutomatic
Cell.Borders (xlEdgeBottom)
Border.LineStyle = xlContinuous
Border.Weight = xlThin
Border.ColorIndex = xlAutomatic
Cell.Borders (xlEdgeRight)
Border.LineStyle = xlContinuous
Border.Weight = xlThin
Border.ColorIndex = xlAutomatic
Cell.Borders (xlInsideVertical)
Border.LineStyle = xlContinuous
Border.Weight = xlThin
Border.ColorIndex = xlAutomatic
Cell.Borders (xlInsideHorizontal)
Border.LineStyle = xlContinuous
Border.Weight = xlThin
Border.ColorIndex = xlAutomatic

End With

 

[Jay Taplin]

I would implement this code as such:

With Range("C510") 'Could change this to "With Selection" (no quotes)
.WrapText = True

.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone

With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

On Error Resume Next

With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

On Error GoTo 0
End With

Please note that the line that states "With Range("C510")" could be
changed "With Selection" (no quotes), so whenever you run the macro it would
work against the selected cells.

Secondly, if I'm understand your intentions, I would not use the AutoFit
method, as this will size your columns out to the maximum width of your
text, then enable wrapping. Ultimately, nothing will be wrapped, unless if
you modify the text later, as the column is already wide enough to
accomodate all the text in it.

Finally, the rest of the code looked pretty good. I added the appropriate
dots (".") to reference the objects and added error handling in the case
that you are only updating the style of one cell.

If you need any more help, please post back.

Jay Taplin MCP