Macro to format multiple worksheets

  • Thread starter Thread starter Chutney
  • Start date Start date
After serious thinking Clif McIrvin wrote :
<...>

I just today stumbled across this new-to-2010 property:

Application.PrintCommunication Property
Specifies whether communication with the printer is turned on. Boolean
Read/write
Version Added: Excel 2010

Set the PrintCommunication property to False to speed up the execution of
code that sets PageSetup properties. Set the PrintCommunication property to
True after setting properties to commit all cached PageSetup commands.
---

I don't recall if the Excel version was mentioned in this thread ... curious
if PrintCommunication=False makes any noticeable difference in the PageSetup
versions of the macros or not.
Click to expand...

Thanks Clif! I didn't know about this property and so will investigate
it more. I've always suspected, though, that PageSetup processing
differed for different printers. During this exercise I was wondering
if setting ActivePrinter="" would be helpful b ut haven't gotten around
to checking it out.

Regardless, McGimpsey's rework of John Green's XL4M solution works
really well and so I'd rather run with that than mess around with
hardware or hardware settings in a 'workaround' fashion when a simple
solution exists already.<g>
 
' GroupSheets()
' This procedure requires only the necessary amount of coding be used
' in the Caller. By default, it requires passing only the first arg.
' Use Example: GroupSheets "Sheet1,Sheet3"
' creates a group of only those sheets.
' To group all sheets in a workbook except those sheets:
' GroupSheets "Sheet1,Sheet3", False
' To group all sheets in a workbook pass an empty string:
' GroupSheets "", False
' You can pass the Wkb arg to specify any open workbook.
' (The Wkb doesn't need to be active for this purpose)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub GroupSheets(sSheetnames As String, _
Optional bInGroup As Boolean = True, _
Optional Wkb As Workbook)
' Groups sheets in Wkb based on whether Sheetnames
' are to be included or excluded in the grouping.
' Arg1 is a comma delimited string. (ie: "Sheet1,Sheet3")

Dim Shts() As String, sz As String
Dim i As Integer, wks As Worksheet, bNameIsIn As Boolean

If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
For Each wks In Wkb.Worksheets
bNameIsIn = (InStr(sSheetnames, wks.Name) > 0)
If bInGroup And bNameIsIn Then sz = wks.Name Else sz = wks.Name
If Not sz = "" Then '//build the array
ReDim Preserve Shts(0 To i): Shts(i) = sz: i = i + 1
End If
Next
ActiveWorkbook.Worksheets(Shts).Select
End Sub
Click to expand...

Looked odd to me ....

I think

If bInGroup Then
If bNameIsIn Then sz = wks.Name Else sz = ""
Else
If bNameIsIn Then sz = "" Else sz = wks.Name
End If

will work better.
 
GS said:
After serious thinking Clif McIrvin wrote :

Thanks Clif! I didn't know about this property and so will investigate
it more. I've always suspected, though, that PageSetup processing
differed for different printers. During this exercise I was wondering
if setting ActivePrinter="" would be helpful b ut haven't gotten
around to checking it out.
Click to expand...


In my testing I noticed no difference when using Generic / Text Only; I
got an error attempting to set ActivePrinter="".
 
Clif McIrvin expressed precisely :
Looked odd to me ....

I think

If bInGroup Then
If bNameIsIn Then sz = wks.Name Else sz = ""
Else
If bNameIsIn Then sz = "" Else sz = wks.Name
End If

will work better.
Click to expand...

Thanks for catching that, Clif! That's actually how it is in my
component. -Must have got messed up doing cut/paste as I have other
code in that same proc which is not needed for this use. -I've now
removed that extraneous code so the entire proc can be copied/pasted in
future.
Here's the complete proc with extra code removed...

Public Sub GroupSheets(Sheetnames As String, _
Optional bInGroup As Boolean = True, _
Optional Wkb As Workbook)
' Groups sheets in Wkb based on whether Sheetnames
' are to be included or excluded in the grouping.
' Arg1 is a comma delimited string. (ie: "Sheet1,Sheet3")

Dim Shts() As String, sz As String
Dim i As Integer, wks As Worksheet, bNameIsIn As Boolean

If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
For Each wks In Wkb.Worksheets
bNameIsIn = (InStr(Sheetnames, wks.name) > 0)
If bInGroup Then
If bNameIsIn Then sz = wks.name
Else
If bNameIsIn Then sz = "" Else sz = wks.name
End If
If Not sz = "" Then '//build the array
ReDim Preserve Shts(0 To i): Shts(i) = sz: i = i + 1
End If
Next
ActiveWorkbook.Worksheets(Shts).Select
End Sub
 
Gary, Jim and Cliff,

I had to drop off line for a while and work on alternative ways of
producing this. As I said in my original post, the workbook was being
produced by Access. In fact, though, the data came originally from a
mainframe via SAS. I went back to SAS (not my strongest area) and
finally worked out how to get it to produce the formatted workbook
directly.

However, I wanted to thank all of you for the tremendous effort that
you put into this. Producing the XL4M solution is very impressive and
original. At some point, though, Microsoft is going to have to come up
with a better mouse trap: even 5 seconds per worksheet is too long.

Regards.
 
Back
Top