View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
GS[_6_] GS[_6_] is offline
external usenet poster
 
Posts: 1,182
Default Formatting multiple sheets within workbook.

Hmm...

Hi Team

Workbook has anywhere from 30 to 40 sheets at any given time, of which approx
that 25 - 30 require formatting.

As there are sheets that cannot be changed I reckon using the VBA.Sheet.name(
Sheet(15) to Sheet(40) ) rather than using ( For each Sheet ).


I use a Const string containing sheetnames that do not get processed...

Const sExclShts$ = "sh1,sh2,sh4,sh7"

For Each sh In ActiveWorkbook.Sheets
If Not InStr(sExclShts, sh.Name) Then
'//process it
End If
Next 'sh

...where sExclShts$ contains the sheetnames not to be processed.

Another approach I use (since sheetnames can be changed during runtime) is to
store a 'tag' as a local scope defined name on all sheets that get some sort of
action taken...

Name: "uiProcess"
RefersTo: "=FormatData"

...and use Application.Evaluate instead of InStr in the loop.

The range for each of these sheets is exactly the same. A10:O28 ( row 10 =
headings ).

Currently, we are doing this manually and it takes anything up to an hour or
more to do, so looking for the magic button to do it in a blink.

Step .1 Filter in ascending order ( this part is easy )


...and so you can manage this yourself???

Step .2 ( The not so easy part. ) In-as-much as all the columns will have
data, the rows may vary from 11 to 28.

Column A = Reference No ( could be 1 row or 6 with the same No. )

Now comes the tricky part.

I would like, so that at the end of each Reference No. in Column "A" the
Bottom Border be double-lined from Columns ("A" to "O"), then continue on
with the next reference No. and so on.


Not understanding how the Reference No's will be contiguous if you Filter on
Col "F"!


IMO:
If your list is contiguous then just read 1 row ahead to see where the change
occurs. Use a For...Next loop so you can use its counter to check the next
row...

Dim n&, vData
vData = ActiveSheet.UsedRange

For n = 10 To 28
If Not vData(n + 1, 1) = vData(n, 1) Then
With Rows(n + 1)
.RowHeight = .RowHeight * 1.5
End With
End If
Next 'n

...where you can apply whatever other formatting you like to the 'change' row
instead of underlining the prior row.

Then, when it gets to the row that is blank, delete the row ( could be 17
blank rows or a mere 1 )


Why are there blanks?

Unless I need a subtotals row between differing records, I typically create a
'visual' separation using RowHeight so the data persists as contiguous since
it's highly likely the table the data is getting added to is a named dynamic
range.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion