Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109952

Re: Formatting multiple sheets within workbook.

Show all headers | View raw

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

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Formatting multiple sheets within workbook. Living the Dream <noodnutt@gmail.com> - 2017-03-28 03:40 -0700
  Re: Formatting multiple sheets within workbook. Living the Dream <noodnutt@gmail.com> - 2017-03-28 03:42 -0700
  Re: Formatting multiple sheets within workbook. GS <gs@v.invalid> - 2017-03-28 10:56 -0400
    Re: Formatting multiple sheets within workbook. Living the Dream <noodnutt@gmail.com> - 2017-03-29 03:40 -0700
      Re: Formatting multiple sheets within workbook. GS <gs@v.invalid> - 2017-03-29 11:48 -0400
        Re: Formatting multiple sheets within workbook. GS <gs@v.invalid> - 2017-03-29 17:46 -0400
          Re: Formatting multiple sheets within workbook. Living the Dream <noodnutt@gmail.com> - 2017-03-30 03:25 -0700
            Re: Formatting multiple sheets within workbook. Claus Busch <claus_busch@t-online.de> - 2017-03-30 12:48 +0200
              Re: Formatting multiple sheets within workbook. Living the Dream <noodnutt@gmail.com> - 2017-03-30 06:16 -0700
                Re: Formatting multiple sheets within workbook. Claus Busch <claus_busch@t-online.de> - 2017-03-30 15:48 +0200
              Re: Formatting multiple sheets within workbook. GS <gs@v.invalid> - 2017-03-30 11:10 -0400
                Re: Formatting multiple sheets within workbook. Living the Dream <noodnutt@gmail.com> - 2017-03-31 04:03 -0700
                Re: Formatting multiple sheets within workbook. GS <gs@v.invalid> - 2017-03-31 07:13 -0400
              Re: Formatting multiple sheets within workbook. Living the Dream <noodnutt@gmail.com> - 2017-03-31 03:51 -0700
                Re: Formatting multiple sheets within workbook. GS <gs@v.invalid> - 2017-03-31 07:08 -0400
    Re: Formatting multiple sheets within workbook. Living the Dream <noodnutt@gmail.com> - 2017-03-29 03:52 -0700
  Re: Formatting multiple sheets within workbook. Living the Dream <noodnutt@gmail.com> - 2017-03-29 02:43 -0700
  Re: Formatting multiple sheets within workbook. Living the Dream <noodnutt@gmail.com> - 2017-04-03 04:12 -0700

csiph-web