Using range names or ranges between quotes

B

Breck

I know that this is probably a very simple issue but I can't find an answer
and everything that I have tried doesn't work. How can I modify this code so
I can either use Excel's "Range Names" ie LftHdrTxt which refers to cell B11
on Sheet2, or ranges Sheet2.Range("B11"). Currently the text LftHdrTxt is
placed in the left header and CtrHdrTxt in the center header etc. The quotes
are required. The PageSetupXl4m defines LeftHead As String with the
following "Optional LeftHead As String," etc. I'm trying to update the
Headers and Footers on 75 sheets from values input by a user in cells, on a
single sheet, in an excel template. Because of different page setup's on
many of the sheets ie portrait on some and landscape on Others. Because of
this I can't simply activate all of the sheets then enter the desired
information in page setup. I'm trying to modify code I found on the
McGimpsey & Associates website. www.mcgimpsey.com/excel/udfs/pagesetup.html.

PageSetupXL4M LeftHead:="LftHdrTxt", CenterHead:="CtrHdrTxt",
RightHead:="RhtHdrTxt", LeftFoot:="LftFtrTxt", CenterFoot:="CtrFtrTxt",
RightFoot:="RhtFtrTxt"

Here is the code for PageSetupXL4M

Public Sub PageSetupXL4M( _
Optional LeftHead As String, _
Optional CenterHead As String, _
Optional RightHead As String, _
Optional LeftFoot As String, _
Optional CenterFoot As String, _
Optional RightFoot As String, _
Optional LeftMarginInches As String, _
Optional RightMarginInches As String, _
Optional TopMarginInches As String, _
Optional BottomMarginInches As String, _
Optional HeaderMarginInches As String, _
Optional FooterMarginInches As String, _
Optional PrintHeadings As String, _
Optional PrintGridlines As String, _
Optional PrintComments As String, _
Optional PrintQuality As String, _
Optional CenterHorizontally As String, _
Optional CenterVertically As String, _
Optional Orientation As String, _
Optional Draft As String, _
Optional PaperSize As String, _
Optional FirstPageNumber As String, _
Optional Order As String, _
Optional BlackAndWhite As String, _
Optional Zoom As String)
'based on a post by John Green in
'microsoft.public.excel.programming
'on 21 January 2001:
'http://google.com/groups?selm=VA.00000b2f.0028c7e5@mara9"
Const c As String = ","
Dim pgSetup As String
Dim head As String
Dim foot As String
If LeftHead <> "" Then head = "&L" & LeftHead
If CenterHead <> "" Then head = head & "&C" & CenterHead
If RightHead <> "" Then head = head & "&R" & RightHead
If Not head="" Then head = """" & head & """"
If LeftFoot <> "" Then foot = "&L" & LeftFoot
If CenterFoot <> "" Then foot = foot & "&C" & CenterFoot
If RightFoot <> "" Then foot = foot & "&R" & RightFoot
If Not foot="" Then foot = """" & foot & """"

pgSetup = "PAGE.SETUP(" & head & c & foot & c & _
LeftMarginInches & c & RightMarginInches & c & _
TopMarginInches & c & BottomMarginInches & c & _
PrintHeadings & c & PrintGridlines & c & _
CenterHorizontally & c & CenterVertically & c & _
Orientation & c & PaperSize & c & Zoom & c & _
FirstPageNumber & c & Order & c & BlackAndWhite & c & _
PrintQuality & c & HeaderMarginInches & c & _
FooterMarginInches & c & PrintComments & c & Draft & ")"
Application.ExecuteExcel4Macro pgSetup
End Sub
 
G

Gary''s Student

If you have defined cell B11 on Sheet1 to have the Name:
be_eleven
then either:

Sub try1()
ActiveSheet.PageSetup.LeftHeader = Range("B11").Value
End Sub

or

Sub try2()
ActiveSheet.PageSetup.LeftHeader = Range("be_eleven").Value
End Sub

will set that left header
 
P

Peter T

I couldn't paste your code as I got "too many continuations". Following
might solve two problems at the same time

' top of module

Type hHeaders
LeftHead As String
CenterHead As String
RightHead As String
LeftFoot As String
CenterFoot As String
RightFoot As String
End Type

Sub abc()
Dim h As tHeaders

h.LeftFoot = Range("LftHdrTxt").Text
h.CenterHead = Range("CtrHdrTxt").Text
' etc

PageSetupXL4M h, next-arg, etc

End Sub

Public Sub PageSetupXL4M(h as hHeaders,

where tHds replaces all the optional strings now included in the 'Type'

you'll need to change things like
If LeftHead <> "" Then head = "&L" & LeftHead
Click to expand...
to
If h.LeftHead <> "" Then head = "&L" & h.LeftHead

Maybe you could combine all those margin arg's in a 'Type'
Public Type mMargins
mLeftMarginInches as string
etc


Regards,
Peter T
 
B

Breck

Gary's Student

I have used pagesetup successfully usuing range names but running
PageSetup on 75 sheets would take 30 minutes or longer because that
method is so slow. The excel4macro page.setup takes 3 minutes. There
have been many posts on this issue with PageSetup.
 
B

Breck

Peter T


I pasted the code from the website verbatum. I had to remove many of
the continuation lines "_" so that 2 or 3 lines of code would be on
one line. I'll try you suggestion latter tonight when I am home.
 
B

Breck

Peter T

I forgot to mention that the Excel4Macro page.setup requires the
information to be in a specific format to work. It has to be formatted
with I think 17 commas separating all of the options that you see if
you opened the page setup dialog box manually
 
P

Peter T

I think 17 commas

It depends which way and for what purpose you are using the XL4 Page.Setup
XL4 macro. I would have thought typically would take 20 arg's with 19 commas
(quick glance at Macrofun.hlp).

However many commas, that has nothing whatsoever to do with what I was
trying to suggest. Firstly how to get the value of a named cell then to use
"Type" to pass many values to the procedure all wrapped up in one argument.

Regards,
Peter T

Peter T

I forgot to mention that the Excel4Macro page.setup requires the
information to be in a specific format to work. It has to be formatted
with I think 17 commas separating all of the options that you see if
you opened the page setup dialog box manually
 
B

Breck

I really appreciate your help. I didn't look at your code thoroughly
until I got home and started putting it together. Then I realized that
you had everything covered blending your new code with the original
code. I think that your count is right I just estimated.

This is what I have. I hope that I understood your instructions and
followed them correctly. I'm getting a "Compile Error" "Argument not
optional" at the end of the First Module on the line that begins
PAGESETUPXL4M2. Once I get this part working I try your suggestion
about the margin arg's.

Why did you suggest "Public Type mMargins" for the margin arg's but
not "Public type hHeaders" for the header/footer arg's. And is all of
the code supposed to be in the same module or does it not matter

First Module

Type hHeaders
LeftHead As String
CenterHead As String
RightHead As String
LeftFoot As String
CenterFoot As String
RightFoot As String
End Type

Sub Update_Header_Footers()
Dim h As hHeaders

h.LeftHead = Range("LftHdrTxt").Text
h.CenterHead = Range("CtrHdrTxt").Text
h.RightHead = Range("RhtHdrTxt").Text
h.LeftFoot = Range("LftFtrTxt").Text
h.CenterFoot = Range("CtrFtrTxt").Text
h.RightFoot = Range("RhtFtrTxt").Text

PageSetupXL4M2 h, LeftMarginInches:=1

End Sub

Second Module

Public Sub PageSetupXL4M2(h As hHeaders, _
tHds, _
Optional LeftMarginInches As String, Optional
RightMarginInches As String, _
Optional TopMarginInches As String, Optional
BottomMarginInches As String, _
Optional HeaderMarginInches As String, Optional
FooterMarginInches As String, _
Optional PrintHeadings As String, Optional PrintGridlines As
String, _
Optional PrintComments As String, _
Optional PrintQuality As String, _
Optional CenterHorizontally As String, Optional
CenterVertically As String, _
Optional Orientation As String, _
Optional Draft As String, _
Optional PaperSize As String, _
Optional FirstPageNumber As String, _
Optional Order As String, _
Optional BlackAndWhite As String, _
Optional Zoom As String)
'based on a post by John Green in
'microsoft.public.excel.programming
'on 21 January 2001:
'http://google.com/groups?selm=VA.00000b2f.0028c7e5@mara9"
Const c As String = ","
Dim pgSetup As String
Dim head As String
Dim foot As String
If LeftHead <> "" Then head = "&L" & h.LeftHead
If CenterHead <> "" Then head = head & "&C" & h.CenterHead
If RightHead <> "" Then head = head & "&R" & h.RightHead
If Not head = "" Then head = """" & head & """"
If LeftFoot <> "" Then foot = "&L" & h.LeftFoot
If CenterFoot <> "" Then foot = foot & "&C" & h.CenterFoot
If RightFoot <> "" Then foot = foot & "&R" & h.RightFoot
If Not foot = "" Then foot = """" & foot & """"

pgSetup = "PAGE.SETUP(" & head & c & foot & c & _
LeftMarginInches & c & RightMarginInches & c & _
TopMarginInches & c & BottomMarginInches & c & _
PrintHeadings & c & PrintGridlines & c & _
CenterHorizontally & c & CenterVertically & c & _
Orientation & c & PaperSize & c & Zoom & c & _
FirstPageNumber & c & Order & c & BlackAndWhite & c & _
PrintQuality & c & HeaderMarginInches & c & _
FooterMarginInches & c & PrintComments & c & Draft & ")"
Application.ExecuteExcel4Macro pgSetup
End Sub
 
B

Breck

I just realized that I should have used PageSetupXL4M2 h,
LeftMarginInches:="1" instead of PageSetupXL4M2 h,
LeftMarginInches:=1
Now I'm getting the error "Constant expression required" on the
pgSetup = line where the "c" is highlighted that is defined as Const c
As String = ","
 
B

Breck

I just replaced all of the "& c &" with "& "," &" and the code ran
without error but didn't update the headers either. I steped through
the code the "tHds", "lefthead", "CenterHead" etc are all empty. They
are not being filled. the Leftmargin
 
B

Breck

I just discovered that I also left off the h. after the "If" and
changed the "tHds" to "hHeader" in the second module. I fell so dumb
it's working great now. Thank you very very much. You have been so
helpful and I appreciate the time that you took to help me out.

Now is there any reason that I can't add additional code to run
PageSetupXL4M2 h, next-arg, etc on every sheet in the workbook.
 
P

Peter T

Glad you got it working. Afraid I gave a hurried explanation to cover quite
a lot, with a typo or two might have thrown you off track.

To run the code on every sheet I think the sheet will need to be active
first. Normally that's not necessary but I suspect with the XL4 macro
that'll be required (I haven't looked into it)

Now that you are into using 'Type' why not make one or two more, such as a
Type to combine all the 'margin' arg's



Sub myPgSetup()
Dim h As hHeaders, m as mMargins
Dim ws as Worksheet

h.LeftHead = Range("LftHdrTxt").Text
h.etc =

m.LeftMarginInches = cStr(number)
m.etc =

For each ws in Activeworkbook.Worksheets
ws.Activate
' could change some arg's depending on the sheet
' If ws.Name = "abc" or ws.Name = "def" Then
' m.LeftMarginInches = "1.2"
' Else m.LeftMarginInches = "2.3"
' End If

PageSetupXL4M2 h, m, nextArg, etc
Next

Regards,
Peter T

I just discovered that I also left off the h. after the "If" and
changed the "tHds" to "hHeader" in the second module. I fell so dumb
it's working great now. Thank you very very much. You have been so
helpful and I appreciate the time that you took to help me out.

Now is there any reason that I can't add additional code to run
PageSetupXL4M2 h, next-arg, etc on every sheet in the workbook.
 
B

Breck

Here is what I have now. The margin settings are not working. As I
step thru the code using debug the "m" has all of the proper margin
numbers in the value. but the "TopMarginInches" etc all have "" in the
value. Also the hHeaders and mMargins both have "missing" in the value
and "Variant/Error" in the type. the c has "Application-defined or
object-defined error" in the value but i'm not using that one now
anyway. What am I missing? Also if I create the name "SheetName" or
something similar for every sheet but define scope as local instead of
workbook would I be able to add that the some of the head header def's
and have it add a unique sheet name to the header?

Public Type hHeaders
LeftHead As String
CenterHead As String
RightHead As String
LeftFoot As String
CenterFoot As String
RightFoot As String
End Type

Public Type mMargins
LeftMarginInches As String
RightMarginInches As String
TopMarginInches As String
BottomMarginInches As String
HeaderMarginInches As String
FooterMarginInches As String
End Type

Sub myPgSetup()
Dim h As hHeaders, m As mMargins
Dim ws As Worksheet

h.LeftHead = Range("LftHdrFnt").Text & Range("LftHdrTxt").Text
h.CenterHead = Range("CtrHdrFnt").Text & Range("CtrHdrTxt").Text
h.RightHead = Range("RhtHdrFnt").Text & Range("RhtHdrTxt").Text
h.LeftFoot = Range("LftFtrFnt").Text & Range("LftFtrTxt").Text
h.CenterFoot = Range("LftFtrFnt").Text & Range("CtrFtrTxt").Text
h.RightFoot = Range("LftFtrFnt").Text & Range("RhtFtrTxt").Text

m.LeftMarginInches = Range("LftMrgn").Value
m.RightMarginInches = Range("RhtMrgn").Value
m.TopMarginInches = Range("TpMrgn").Value
m.BottomMarginInches = Range("BtmMrgn").Value
m.HeaderMarginInches = Range("HdrMrgn").Value
m.FooterMarginInches = Range("FtrMrgn").Value

PageSetupXL4M2 h, m

End Sub

Public Sub PageSetupXL4M2(h As hHeaders, m As mMargins, _
Optional hHeaders, Optional mMargins, _
Optional LeftMarginInches As String, Optional
RightMarginInches As String, _
Optional TopMarginInches As String, Optional
BottomMarginInches As String, _
Optional HeaderMarginInches As String, Optional
FooterMarginInches As String, _
Optional PrintHeadings As String, Optional PrintGridlines As
String, _
Optional PrintComments As String, _
Optional PrintQuality As String, _
Optional CenterHorizontally As String, Optional
CenterVertically As String, _
Optional Orientation As String, _
Optional Draft As String, _
Optional PaperSize As String, _
Optional FirstPageNumber As String, _
Optional Order As String, _
Optional BlackAndWhite As String, _
Optional Zoom As String)
'based on a post by John Green in
'microsoft.public.excel.programming
'on 21 January 2001:
'http://google.com/groups?selm=VA.00000b2f.0028c7e5@mara9"
Const c As String = ","
Dim pgSetup As String
Dim head As String
Dim foot As String
If h.LeftHead <> "" Then head = "&L" & h.LeftHead
If h.CenterHead <> "" Then head = head & "&C" & h.CenterHead
If h.RightHead <> "" Then head = head & "&R" & h.RightHead
If Not head = "" Then head = """" & head & """"
If h.LeftFoot <> "" Then foot = "&L" & h.LeftFoot
If h.CenterFoot <> "" Then foot = foot & "&C" & h.CenterFoot
If h.RightFoot <> "" Then foot = foot & "&R" & h.RightFoot
If Not foot = "" Then foot = """" & foot & """"

pgSetup = "PAGE.SETUP(" & head & "," & foot & "," & _
m.LeftMarginInches & "," & m.RightMarginInches & "," & _
m.TopMarginInches & "," & m.BottomMarginInches & "," & _
PrintHeadings & "," & PrintGridlines & "," & _
CenterHorizontally & "," & CenterVertically & "," & _
Orientation & "," & PaperSize & "," & Zoom & "," & _
FirstPageNumber & "," & Order & "," & BlackAndWhite & "," & _
PrintQuality & "," & m.HeaderMarginInches & "," & _
m.FooterMarginInches & "," & PrintComments & "," & Draft & ")"
Application.ExecuteExcel4Macro pgSetup
End Sub
 
P

Peter T

Not sure why it isn't working for you but looks like you can remove quite a
few more of those optional arg's which are now replaced with the two type
arguments, 'h' & 'm'

Also, leave in PageSetupXL4M2(
h As hHeaders, m As mMargins,
but take out
Optional hHeaders, Optional mMargins,
as well as the other now redundant arg's

Following seems to work fine for me -

Option Explicit
Public Type hHeaders
LeftHead As String
CenterHead As String
RightHead As String
LeftFoot As String
CenterFoot As String
RightFoot As String
End Type

Public Type mMargins
LeftMarginInches As String
RightMarginInches As String
TopMarginInches As String
BottomMarginInches As String
HeaderMarginInches As String
FooterMarginInches As String
End Type

Sub myPgSetup()
Dim h As hHeaders, m As mMargins
Dim ws As Worksheet
Const cm As Single = 1 / 2.5

h.LeftHead = "LftHdrFnt + LftHdrTxt"
h.CenterHead = "CtrHdrFnt + CtrHdrTxt"
h.RightHead = "RhtHdrFnt + RhtHdrTxt"
h.LeftFoot = "LftFtrFnt + ftFtrTxt"
h.CenterFoot = "LftFtrFnt + CtrFtrTxt"
h.RightFoot = "LftFtrFnt + RhtFtrTxt"

If Application.International(xlMetric) Then
m.LeftMarginInches = Round(2.2 * cm, 2)
m.RightMarginInches = Round(2.4 * cm, 2)
m.TopMarginInches = Round(2.6 * cm, 2)
m.BottomMarginInches = Round(2.8 * cm, 2)
m.HeaderMarginInches = Round(1.3 * cm, 2)
m.FooterMarginInches = Round(1.7 * cm, 2)
Else
m.LeftMarginInches = 1.2
m.RightMarginInches = 1.4
m.TopMarginInches = 1.6
m.BottomMarginInches = 1.8
m.HeaderMarginInches = 0.7
m.FooterMarginInches = 0.9
End If

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
PageSetupXL4M2 h, m
Next
End Sub

Public Sub PageSetupXL4M2(h As hHeaders, m As mMargins, _
Optional PrintHeadings As String, _
Optional PrintGridlines As String, _
Optional PrintComments As String, _
Optional PrintQuality As String, _
Optional CenterHorizontally As String, _
Optional CenterVertically As String, _
Optional Orientation As String, _
Optional Draft As String, _
Optional PaperSize As String, _
Optional FirstPageNumber As String, _
Optional Order As String, _
Optional BlackAndWhite As String, _
Optional Zoom As String)
'based on a post by John Green in
'microsoft.public.excel.programming
'on 21 January 2001:
'http://google.com/groups?selm=VA.00000b2f.0028c7e5@mara9"
Const c As String = ","
Dim pgSetup As String
Dim head As String
Dim foot As String
If h.LeftHead <> "" Then head = "&L" & h.LeftHead
If h.CenterHead <> "" Then head = head & "&C" & h.CenterHead
If h.RightHead <> "" Then head = head & "&R" & h.RightHead
If Not head = "" Then head = """" & head & """"
If h.LeftFoot <> "" Then foot = "&L" & h.LeftFoot
If h.CenterFoot <> "" Then foot = foot & "&C" & h.CenterFoot
If h.RightFoot <> "" Then foot = foot & "&R" & h.RightFoot
If Not foot = "" Then foot = """" & foot & """"

pgSetup = "PAGE.SETUP(" & head & "," & foot & "," & _
m.LeftMarginInches & "," & m.RightMarginInches & "," & _
m.TopMarginInches & "," & m.BottomMarginInches & "," & _
PrintHeadings & "," & PrintGridlines & "," & _
CenterHorizontally & "," & CenterVertically & "," & _
Orientation & "," & PaperSize & "," & Zoom & "," & _
FirstPageNumber & "," & Order & "," & BlackAndWhite & "," & _
PrintQuality & "," & m.HeaderMarginInches & "," & _
m.FooterMarginInches & "," & PrintComments & "," & Draft & ")"
Application.ExecuteExcel4Macro pgSetup
End Sub


Obviously those inches & cms values are not equivalent, only like that to
make it easy to validate correct values are applied depending on your
regional settings. I didn't bother recreating your named ranges to get the
values, just the hard coded strings for testing, but that's another matter.

Regards,
Peter T
 
B

Breck

Success at last I can't believe how good its working it takes just 65
seconds to update the page settings of all 75 pages. Since you weren't
having problem I made 2 changes. I changed the format of the cell that
had the margin settings to "general" then I changes all of the
m.margin setting from .value to .text ran the macro and it ran
perfectly. Well almost perfectly. I introduced another twist I added
more to the CenterHead line

h.CenterHead = Range("CtrHdrFnt").Text & Range("CtrHdrTxt").Text & Chr
(10) & Range("CtrHdrPNFnt").Text & Range("ShtNm").Text

where ShtNm is a range name on each sheet that has the scope set as
the individual sheet instead of the usual workbook just like the
"Print_Area" setting are sheet specific. the macro used the first
sheets "ShtNm" instead of each sheets "ShtNm"

I also turned off events so the Workbook_Activate wouldn't execute on
the sheets that used Workbook_Activate to trigger additional code.
Wasn't sure where the best place for it would be though.

For Each ws In ActiveWorkbook.Worksheets
Application.EnableEvents = False
ws.Activate
PageSetupXL4M2 h, m
Application.EnableEvents = True
Next

I thought that the ws.Activate would allow this to work since you can
watch the macro cycle through all of the sheets in the workbook. Not
that the rest of the code is working so well I get to figure this one
out now. Most the the "ShtNm" refer to cells A1 on that sheet but
there are 15 sheets that refer to C1. The sheets with C1 can be
changed if necessary to A1 but I'm not sure yet if that will help.

I can't thank you enough for your help. You have been so kind. I'm
creating a volleyball stats program gratis for the girls Intermountain
Volleyball Association. They will be offering the workbook to
volleyball clubs and high school coaches in the region for a small fee
that goes into a scholarship fund. We are hoping that it will not only
help some girls but also help improve the play of all of the girls by
tracking the individual and team statistics.
 
B

Breck

sorry type Not that the rest of the code is working so well
should be Now that the rest of the code is working so well
 
P

Peter T

Sounds like you're well up and running. Just a few comments -
I introduced another twist I added
more to the CenterHead line
Click to expand...
h.CenterHead = Range("CtrHdrFnt").Text & Range("CtrHdrTxt").Text &
Chr (10) & Range("CtrHdrPNFnt").Text & Range("ShtNm").Text
Click to expand...
where ShtNm is a range name on each sheet that has the scope set as
the individual sheet instead of the usual workbook
Click to expand...

As you know, Worksheet or 'local' level names allow to use the same name to
define a range on each sheet, defined like
'SheetName'!theName

ensure you are doing
h.CenterHead = Range("CtrHdrFnt").Text etc
only when the specific sheet is active. That means code like the above
should be in the worksheet loop just after the relevant sheet has been
activated. You could say, assign some of the 'Type' variables, which apply
to all sheets before the loop, then specific sheet variables in the loop
after activating the sheet. Not sure if you are doing that already, if not
you should.

I also turned off events so the Workbook_Activate wouldn't execute on
the sheets that used Workbook_Activate to trigger additional code.
Click to expand...
Wasn't sure where the best place for it would be though.
For Each ws In ActiveWorkbook.Worksheets
Application.EnableEvents = False
ws.Activate
PageSetupXL4M2 h, m
Application.EnableEvents = True
Next
Click to expand...

No need to change EnableEvents in each loop

Application.EnableEvents = False
ws.Activate
' populate any variables that take data from worksheet level
' names here
' though the name would need to exist on each sheet
PageSetupXL4M2 h, m
Next

Application.EnableEvents = True

(Typically an EnableEvents would be reset after an error handler. But in
your case probably best not, if something goes wrong you'll find out quickly
and easily. Be sure to reset EnableEvents manually if necessary (ie if code
fails to finish normally)
They will be offering the workbook to
volleyball clubs and high school coaches in the region for a small
fee that goes into a scholarship fund.
Click to expand...

That's great :)

Regards,
Peter T


Success at last I can't believe how good its working it takes just 65
seconds to update the page settings of all 75 pages. Since you weren't
having problem I made 2 changes. I changed the format of the cell that
had the margin settings to "general" then I changes all of the
m.margin setting from .value to .text ran the macro and it ran
perfectly. Well almost perfectly. I introduced another twist I added
more to the CenterHead line

h.CenterHead = Range("CtrHdrFnt").Text & Range("CtrHdrTxt").Text & Chr
(10) & Range("CtrHdrPNFnt").Text & Range("ShtNm").Text

where ShtNm is a range name on each sheet that has the scope set as
the individual sheet instead of the usual workbook just like the
"Print_Area" setting are sheet specific. the macro used the first
sheets "ShtNm" instead of each sheets "ShtNm"

I also turned off events so the Workbook_Activate wouldn't execute on
the sheets that used Workbook_Activate to trigger additional code.
Wasn't sure where the best place for it would be though.

For Each ws In ActiveWorkbook.Worksheets
Application.EnableEvents = False
ws.Activate
PageSetupXL4M2 h, m
Application.EnableEvents = True
Next

I thought that the ws.Activate would allow this to work since you can
watch the macro cycle through all of the sheets in the workbook. Not
that the rest of the code is working so well I get to figure this one
out now. Most the the "ShtNm" refer to cells A1 on that sheet but
there are 15 sheets that refer to C1. The sheets with C1 can be
changed if necessary to A1 but I'm not sure yet if that will help.

I can't thank you enough for your help. You have been so kind. I'm
creating a volleyball stats program gratis for the girls Intermountain
Volleyball Association. They will be offering the workbook to
volleyball clubs and high school coaches in the region for a small fee
that goes into a scholarship fund. We are hoping that it will not only
help some girls but also help improve the play of all of the girls by
tracking the individual and team statistics.
 
Top