A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Formatting multiple sheets within workbook.



 
 
Thread Tools Display Modes
  #11  
Old March 30th 17, 02:16 PM posted to microsoft.public.excel.programming
Living the Dream
external usenet poster
 
Posts: 119
Default Formatting multiple sheets within workbook.

Hi Claus

For the life of me, I have no idea why, but this code has gone to shyte.

Was working relatively fine, now it keeps halting.

1. If there are no Blank rows to delete, the code halts.
( probably need an exit if criteria not met ).
2. It won't sort now
( most likely has to do with overall used range, as will not always be rows 11 to 26, sometimes more, sometimes less).
3. the double-lines don't appear as before.
( clueless as to why this has stopped working ).

Too tired to worry about it, time for bed, thank you for you effor though.

Cheers
Mark.

Sigh..Back to the drawing board again.


Ads
  #12  
Old March 30th 17, 02:48 PM posted to microsoft.public.excel.programming
Claus Busch
external usenet poster
 
Posts: 3,448
Default Formatting multiple sheets within workbook.

Hi Mark,

Am Thu, 30 Mar 2017 06:16:50 -0700 (PDT) schrieb Living the Dream:

> 1. If there are no Blank rows to delete, the code halts.
> ( probably need an exit if criteria not met ).
> 2. It won't sort now
> ( most likely has to do with overall used range, as will not always be rows 11 to 26, sometimes more, sometimes less).
> 3. the double-lines don't appear as before.
> ( clueless as to why this has stopped working ).


try:

For Each sh In ActiveWorkbook.Sheets
If InStr(sExclShts, sh.Name) Then
With sh
Set rng = .Range("A10:O26")
rng.Borders(xlEdgeBottom).LineStyle = xlNone
rng.Sort key1:=.Range("F10"), order1:=xlAscending, Header:=xlYes

For i = 11 To 26
If .Cells(i + 1, "A") <> .Cells(i, "A") Then
With .Range(.Cells(i, 1), .Cells(i, 14))
With .Borders(xlEdgeBottom)
.LineStyle = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThick
End With
End With
End If
Next
For i = 26 To 11 Step -1
If Len(.Cells(i, 1)) = 0 Then
.Rows(i).Delete
End If
Next i
End With
End If
Next sh


Regards
Claus B.
--
Windows10
Office 2016
  #13  
Old March 30th 17, 04:10 PM posted to microsoft.public.excel.programming
GS[_6_]
external usenet poster
 
Posts: 695
Default Formatting multiple sheets within workbook.

Claus,
Mark is looking to act only on sheets NOT in the sExclShts delimited list!

Also, I've found (occasionally) some anomolies occur using nested 'With'
constructs in respect to object refs. not sure about the why/how of it so I
make a policy when anomolies occur to add a fully qualified ref in nested
statements that require the 'End' statement.

Another serious issue I see here is poor variable declaration! Mark, you need
to go into the Options dialog and checkmark the "Require variable declaration"
opption on the 'General' tab.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #14  
Old March 31st 17, 11:51 AM posted to microsoft.public.excel.programming
Living the Dream
external usenet poster
 
Posts: 119
Default Formatting multiple sheets within workbook.

Hi Claus

I bypassed the For sh and used Activesheet and ran the code individually for each of the sheet I needed foramtting and I have to say it worked very well.

Now, all that needs doing is this exclusion list and I can roll it out and not have to stress over sheet that I don't want touched.

I must be doing something wrong with Garry's Cont sh Statement in that it is either looking for the VB.name of the sheets, or the actual name on the Sheet.tab.

Either way, it made a huge difference to my night cutting around 45 minutes off this particular process.

Thank you to both you and Garry for you patience and guidance.

Cheers
Mark.
  #15  
Old March 31st 17, 12:03 PM posted to microsoft.public.excel.programming
Living the Dream
external usenet poster
 
Posts: 119
Default Formatting multiple sheets within workbook.

Hi Garry

I understand your concerns with declaring, and if I was churning out this stuff for a livelihood I would probably pay more attention to it, but!

In that this is a side thing I actually don't get paid to do for my cheap-ass employer who at times gives me 5 minutes to do the impossible, I skrimp and scrape and use the melting-pot to butcher out this stuff on time.

Mostly it's for my own usage and short-term it will be from here-on as the company are currently spending millions on an entirely purpose designed and built system called Ramco to handle every conceivable corner of business activity so most of what I have done will go out with the trash.

One day I might even retire and go back to school and actually learn what it is I do with VB Excel and Access. But that's not likely to happen for a while.

Cheers
Mark.
  #16  
Old March 31st 17, 12:08 PM posted to microsoft.public.excel.programming
GS[_6_]
external usenet poster
 
Posts: 695
Default Formatting multiple sheets within workbook.

> I must be doing something wrong with Garry's Cont sh Statement in that it is
> either looking for the VB.name of the sheets, or the actual name on the
> Sheet.tab


I use the sheet tab name in the list. The other name you refer to is the
'CodeName'. Again, I recommend 'tagging' the process as a local scope defined
name like "uiProcess" and set its RefersTo for the task to be acted on for the
sheet[s]. You can also include multiple processes if need be o long as your
business logic is well structured to manage this!

Note that both TabName and CodeName can be changed; -in the case of custom
CodeNames Excel may reset those to default names unexpectedly.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #17  
Old March 31st 17, 12:13 PM posted to microsoft.public.excel.programming
GS[_6_]
external usenet poster
 
Posts: 695
Default Formatting multiple sheets within workbook.

> Hi Garry
>
> I understand your concerns with declaring, and if I was churning out this
> stuff for a livelihood I would probably pay more attention to it, but!
>
> In that this is a side thing I actually don't get paid to do for my cheap-ass
> employer who at times gives me 5 minutes to do the impossible, I skrimp and
> scrape and use the melting-pot to butcher out this stuff on time.
>
> Mostly it's for my own usage and short-term it will be from here-on as the
> company are currently spending millions on an entirely purpose designed and
> built system called Ramco to handle every conceivable corner of business
> activity so most of what I have done will go out with the trash.
>
> One day I might even retire and go back to school and actually learn what it
> is I do with VB Excel and Access. But that's not likely to happen for a
> while.
>
> Cheers
> Mark.


It would just be doing yourself a favor to require variable declaration because
your VBE will let you know when things won't compile. Also, it saves having to
remember variable names because intellisense will display them after typing a
few letters and pressing Ctrl+J! You only have to check the box once and the
VBE automatically sets that up for you thereafter!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #18  
Old April 3rd 17, 12:12 PM posted to microsoft.public.excel.programming
Living the Dream
external usenet poster
 
Posts: 119
Default Formatting multiple sheets within workbook.

Garry & Claus

Just wanted to say thank you to both of you for your guidance. I decided to forego the Exclusion list and went with a table list.

Ran it tonight at work and it went very well. Here is what I ended up with just in case anyone in the future may look at doing something similar.

Sub Sheet_Magic()

Dim rng, c
Dim RSrng, rCell
Dim sh As Worksheet

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set RSrng = Sheets("Route Summary").Range("A5:A43")

For Each rCell In RSrng

On Error Resume Next
If Not rCell.Value Is Nothing Then Sheets("" & rCell).Activate
On Error GoTo 0

With ActiveSheet
Set rng = .Range("A10:O26")
rng.Borders(xlEdgeBottom).LineStyle = xlNone
rng.Sort key1:=.Range("F10"), order1:=xlAscending, Header:=xlYes

For i = 11 To 26
If .Cells(i + 1, "A") <> .Cells(i, "A") Then
With .Range(.Cells(i, 1), .Cells(i, 14))
With .Borders(xlEdgeBottom)
.LineStyle = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThick
End With
End With
End If
Next
For i = 26 To 11 Step -1
If Len(.Cells(i, 1)) = 0 Then
.Rows(i).Delete
End If
Next
End With
Next rCell

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Open multiple workbook then combine into single workbook butdifferent sheets geniusideas Excel Programming 10 July 4th 12 04:45 AM
formatting sheets in a workbook SM_NCSW Excel Worksheet Functions 4 November 6th 08 10:03 PM
Automated multiple text files into multiple sheets in one workbook Dr Dan Excel Discussion (Misc queries) 14 November 4th 07 11:32 AM
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA Amaxwell Excel Worksheet Functions 4 August 17th 06 06:23 AM
How do I copy print formatting to multiple sheets in a workbook? [email protected] Excel Discussion (Misc queries) 2 March 29th 06 01:34 AM


All times are GMT +1. The time now is 12:41 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.