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.
|