Standardizing Page Set Up for Muliple Sheets
 

I want the same header, footer and column headings on about 8 sheets, but I
can't seem to get it to work. If I list just one page in the string it does,
but if I list all the sheets, it does not. Here is just a section of my code.
What am I doing wrong. Thanks for your help.

Sub PageSetup()

Sheets("NEW CONFIRM", "NEW GESV", "NEW GESA").Select

Rows("1:1").Select
Selection.Insert Shift:=xlDown

Range("A1").Select
ActiveCell.FormulaR1C1 = "Match/No Match"

Range("B1").Select
ActiveCell.FormulaR1C1 = "Original Table"

Range("C1").Select
ActiveCell.FormulaR1C1 = "CNO"

Range("D1").Select
ActiveCell.FormulaR1C1 = "Name"

Standardizing Page Set Up for Muliple Sheets
 

Some things work with grouped sheets when you do them manually. Some things
don't.

But you can always loop through your sheets:

Option Explicit
Sub PageSetup()
Dim wks As Worksheet

For Each wks In Worksheets(Array("NEW CONFIRM", "NEW GESV", "NEW GESA"))
With wks
.Rows(1).Insert
.Range("a1").Value = "Match/No Match"
.Range("B1").Value = "Original Table"
.Range("C1").Value = "CNO"
.Range("D1").Value = "Name"
End With
Next wks

End Sub



JOUIOUI wrote:

I want the same header, footer and column headings on about 8 sheets, but I
can't seem to get it to work. If I list just one page in the string it does,
but if I list all the sheets, it does not. Here is just a section of my code.
What am I doing wrong. Thanks for your help.

Sub PageSetup()

Sheets("NEW CONFIRM", "NEW GESV", "NEW GESA").Select

Rows("1:1").Select
Selection.Insert Shift:=xlDown

Range("A1").Select
ActiveCell.FormulaR1C1 = "Match/No Match"

Range("B1").Select
ActiveCell.FormulaR1C1 = "Original Table"

Range("C1").Select
ActiveCell.FormulaR1C1 = "CNO"

Range("D1").Select
ActiveCell.FormulaR1C1 = "Name"

--

Dave Peterson

Standardizing Page Set Up for Muliple Sheets
 

Hi Dave,

Thanks for the advice. I used the Option Explicit just as you have below
and it jumps up to my previous sub and doesn't work. I read about the
statement and that it has to appear after an end sub but I just can't seem to
get it to work. Do I have to do anything special since a sub appears before
it?

Thanks

"Dave Peterson" wrote:

Some things work with grouped sheets when you do them manually. Some things
don't.

But you can always loop through your sheets:

Option Explicit
Sub PageSetup()
Dim wks As Worksheet

For Each wks In Worksheets(Array("NEW CONFIRM", "NEW GESV", "NEW GESA"))
With wks
.Rows(1).Insert
.Range("a1").Value = "Match/No Match"
.Range("B1").Value = "Original Table"
.Range("C1").Value = "CNO"
.Range("D1").Value = "Name"
End With
Next wks

End Sub



JOUIOUI wrote:

I want the same header, footer and column headings on about 8 sheets, but I
can't seem to get it to work. If I list just one page in the string it does,
but if I list all the sheets, it does not. Here is just a section of my code.
What am I doing wrong. Thanks for your help.

Sub PageSetup()

Sheets("NEW CONFIRM", "NEW GESV", "NEW GESA").Select

Rows("1:1").Select
Selection.Insert Shift:=xlDown

Range("A1").Select
ActiveCell.FormulaR1C1 = "Match/No Match"

Range("B1").Select
ActiveCell.FormulaR1C1 = "Original Table"

Range("C1").Select
ActiveCell.FormulaR1C1 = "CNO"

Range("D1").Select
ActiveCell.FormulaR1C1 = "Name"

--

Dave Peterson

Standardizing Page Set Up for Muliple Sheets
 

JOUIOUI wrote:
Thanks for the advice. I used the Option Explicit just as you have below
and it jumps up to my previous sub and doesn't work. I read about the
statement and that it has to appear after an end sub but I just can't seem to
get it to work. Do I have to do anything special since a sub appears before
it?

In standalone VB, the option explicit statement normally appears in the source
before /all/ procedures of any kind. I think it actually has to.
Personally, I never allow anything to come before it in source
except on RARE occasions maybe a /few/ lines of comments.

I don't know if it's the same in Excel but I think it very likely.

I see VB6 throws this error dialog:
---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

Only comments may appear after End Sub, End Function, or End Property
---------------------------
OK Help
---------------------------



Bob
--

Standardizing Page Set Up for Muliple Sheets
 

Put that line "Option Explicit" at the top of the module.

It's a directive to the compiler that tells it to expect that any variable will
be declared (Dim or Const or...)

Declaring variables will actually make your life easier. Instead of spending
hours trying to find why some piece of code doesn't work the way you expect it,
many times the code won't even compile--this is very useful to avoid common
spelling errors in variables.



JOUIOUI wrote:

Hi Dave,

Thanks for the advice. I used the Option Explicit just as you have below
and it jumps up to my previous sub and doesn't work. I read about the
statement and that it has to appear after an end sub but I just can't seem to
get it to work. Do I have to do anything special since a sub appears before
it?

Thanks

"Dave Peterson" wrote:

Some things work with grouped sheets when you do them manually. Some things
don't.

But you can always loop through your sheets:

Option Explicit
Sub PageSetup()
Dim wks As Worksheet

For Each wks In Worksheets(Array("NEW CONFIRM", "NEW GESV", "NEW GESA"))
With wks
.Rows(1).Insert
.Range("a1").Value = "Match/No Match"
.Range("B1").Value = "Original Table"
.Range("C1").Value = "CNO"
.Range("D1").Value = "Name"
End With
Next wks

End Sub



JOUIOUI wrote:

I want the same header, footer and column headings on about 8 sheets, but I
can't seem to get it to work. If I list just one page in the string it does,
but if I list all the sheets, it does not. Here is just a section of my code.
What am I doing wrong. Thanks for your help.

Sub PageSetup()

Sheets("NEW CONFIRM", "NEW GESV", "NEW GESA").Select

Rows("1:1").Select
Selection.Insert Shift:=xlDown

Range("A1").Select
ActiveCell.FormulaR1C1 = "Match/No Match"

Range("B1").Select
ActiveCell.FormulaR1C1 = "Original Table"

Range("C1").Select
ActiveCell.FormulaR1C1 = "CNO"

Range("D1").Select
ActiveCell.FormulaR1C1 = "Name"

--

Dave Peterson


--

Dave Peterson

Standardizing Page Set Up for Muliple Sheets
 

Thanks Dave, I truly appeciate the explanation...This is working great now.
Have a great holiday :-)

"Dave Peterson" wrote:

Put that line "Option Explicit" at the top of the module.

It's a directive to the compiler that tells it to expect that any variable will
be declared (Dim or Const or...)

Declaring variables will actually make your life easier. Instead of spending
hours trying to find why some piece of code doesn't work the way you expect it,
many times the code won't even compile--this is very useful to avoid common
spelling errors in variables.



JOUIOUI wrote:

Hi Dave,

Thanks for the advice. I used the Option Explicit just as you have below
and it jumps up to my previous sub and doesn't work. I read about the
statement and that it has to appear after an end sub but I just can't seem to
get it to work. Do I have to do anything special since a sub appears before
it?

Thanks

"Dave Peterson" wrote:

Some things work with grouped sheets when you do them manually. Some things
don't.

But you can always loop through your sheets:

Option Explicit
Sub PageSetup()
Dim wks As Worksheet

For Each wks In Worksheets(Array("NEW CONFIRM", "NEW GESV", "NEW GESA"))
With wks
.Rows(1).Insert
.Range("a1").Value = "Match/No Match"
.Range("B1").Value = "Original Table"
.Range("C1").Value = "CNO"
.Range("D1").Value = "Name"
End With
Next wks

End Sub



JOUIOUI wrote:

I want the same header, footer and column headings on about 8 sheets, but I
can't seem to get it to work. If I list just one page in the string it does,
but if I list all the sheets, it does not. Here is just a section of my code.
What am I doing wrong. Thanks for your help.

Sub PageSetup()

Sheets("NEW CONFIRM", "NEW GESV", "NEW GESA").Select

Rows("1:1").Select
Selection.Insert Shift:=xlDown

Range("A1").Select
ActiveCell.FormulaR1C1 = "Match/No Match"

Range("B1").Select
ActiveCell.FormulaR1C1 = "Original Table"

Range("C1").Select
ActiveCell.FormulaR1C1 = "CNO"

Range("D1").Select
ActiveCell.FormulaR1C1 = "Name"

--

Dave Peterson


--

Dave Peterson