View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Living the Dream Living the Dream is offline
external usenet poster
 
Posts: 151
Default Formatting multiple sheets within workbook.

Hi Garry

I had a play with your code and it seems to work, but!!!!

It keeps stopping at the same line as I mentioned in the other code you supplied for another project I had with the Error 9 Subscript out of range..

If Not vData(n + 1, 1) = vData(n, 1) Then

This is the code thus far. I have still yet to get to the part where it deletes blank rows.

Sub Sheet_Magic()

Const sExclShts$ = "'TMS DATA', 'SUPPORT DATA', 'TMS AUDIT', 'TENDER AMOUNTS', 'ROUTE SUMMARY', 'RUN SHEET TEMPLATE', OVERWEIGHT, 'COMMIT LOG', 'TENDER LOG'"

For Each sh In ActiveWorkbook.Sheets
If Not InStr(sExclShts, sh.Name) Then

Dim n&, vData
vData = ActiveSheet.UsedRange

With ActiveSheet
.Range(Cells(11, 1), Cells(26, 14)).Sort key1:=.Cells(4, 6)
End With

For n = 10 To 26
If Not vData(n + 1, 1) = vData(n, 1) Then
With Rows(n + 1).Columns("A:N").Borders(xlEdgeBottom)
.LineStyle = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThick
End With
End If
Next n

End If
Next sh

End Sub

Any thoughts as to why it keeps halting on the vData line..??

Look forward to hearing from you soon.

Many thanks again.
Mark.