vba problem when grouping sheets
 

If I group sheets together and then make an entry on any one of the grouped
sheets the entry is replicated throughout the grouped sheets.
I have done this whilst recording a macro and then (after stopping the
recording) I have cleared the cells
However if I run the macro the sheets are grouped together but the entry
only appears on the active sheet.
Why is this ?

Windows XP and Office 2003 Pro

vba problem when grouping sheets
 

would you post the code you recorded?

"JB" wrote:

If I group sheets together and then make an entry on any one of the grouped
sheets the entry is replicated throughout the grouped sheets.
I have done this whilst recording a macro and then (after stopping the
recording) I have cleared the cells
However if I run the macro the sheets are grouped together but the entry
only appears on the active sheet.
Why is this ?

Windows XP and Office 2003 Pro

vba problem when grouping sheets
 

Thank you for your response,
I decided to start afresh before I read your reply and now I can Change the
value of a cell throughout the grouped worksheets but I am experiencing the
same problem with custom page headers for a group of worksheets.

Here is the relevant code in the hope you can identify where I am going
wrong
Regards
.................................................. ..............................................
Private Sub CommandButton1_Click()

If ComboBox1.ListIndex = -1 Then Exit Sub
If ComboBox2.ListIndex = -1 Then Exit Sub
If TextBox2 = "" Then Exit Sub
Me.Hide

If ComboBox1.ListIndex = 0 Then 'Print All Outlets

Dim Arr() As String 'Create an Array
of all Outlet Sheets
Dim N As Long

With ThisWorkbook.Worksheets
ReDim Arr(3 To .Count)
For N = 3 To .Count
Arr(N) = .Item(N).Name
Next N
End With
Sheets(Arr).Select

If ComboBox2.ListIndex = 0 Then 'Print All Months
For m = 3 To 14
With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius, Bold
Italic""&14" & Sheets("variables").Cells(m, 2) & " " & TextBox2
End With
Sheets(Arr).PrintPreview
' Sheets(Arr).PrintOut
Next m
Else 'Print Selected Month
ActiveSheet.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & ComboBox2.Value & " " & TextBox2
Sheets(Arr).PrintPreview
' Sheets(Arr).PrintOut
End If
Sheets(1).Activate

Else 'Print selected
Outlet
With Sheets(ComboBox1.Value)
If ComboBox2.ListIndex = 0 Then 'Print All Months
For m = 3 To 5 '14
.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & Sheets("variables").Cells(m, 2) & " " &
TextBox2
.PrintPreview
' .PrintOut
Next m
Else 'Print Selected
Month
.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & ComboBox2.Value & " " & TextBox2
.PrintPreview
' .PrintOut
End If
End With

End If
Me.Show

End Sub

.................................................. .........


"JLGWhiz" wrote in message
...
would you post the code you recorded?

"JB" wrote:

If I group sheets together and then make an entry on any one of the
grouped
sheets the entry is replicated throughout the grouped sheets.
I have done this whilst recording a macro and then (after stopping the
recording) I have cleared the cells
However if I run the macro the sheets are grouped together but the entry
only appears on the active sheet.
Why is this ?

Windows XP and Office 2003 Pro

vba problem when grouping sheets
 

Perhaps I should have identified the line of code that doesn't work as I
expected .... it is:
...................................
With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius, Bold
Italic""&14" & Sheets("variables").Cells(m, 2) & " " & TextBox2
End With
...................................
TIA


"JB" wrote in message
. uk...
Thank you for your response,
I decided to start afresh before I read your reply and now I can Change
the value of a cell throughout the grouped worksheets but I am
experiencing the same problem with custom page headers for a group of
worksheets.

Here is the relevant code in the hope you can identify where I am going
wrong
Regards
.................................................. .............................................
Private Sub CommandButton1_Click()

If ComboBox1.ListIndex = -1 Then Exit Sub
If ComboBox2.ListIndex = -1 Then Exit Sub
If TextBox2 = "" Then Exit Sub
Me.Hide

If ComboBox1.ListIndex = 0 Then 'Print All Outlets

Dim Arr() As String 'Create an
Array of all Outlet Sheets
Dim N As Long

With ThisWorkbook.Worksheets
ReDim Arr(3 To .Count)
For N = 3 To .Count
Arr(N) = .Item(N).Name
Next N
End With
Sheets(Arr).Select

If ComboBox2.ListIndex = 0 Then 'Print All Months
For m = 3 To 14
With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius, Bold
Italic""&14" & Sheets("variables").Cells(m, 2) & " " & TextBox2
End With
Sheets(Arr).PrintPreview
' Sheets(Arr).PrintOut
Next m
Else 'Print Selected Month
ActiveSheet.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & ComboBox2.Value & " " & TextBox2
Sheets(Arr).PrintPreview
' Sheets(Arr).PrintOut
End If
Sheets(1).Activate

Else 'Print selected
Outlet
With Sheets(ComboBox1.Value)
If ComboBox2.ListIndex = 0 Then 'Print All Months
For m = 3 To 5 '14
.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & Sheets("variables").Cells(m, 2) & " " &
TextBox2
.PrintPreview
' .PrintOut
Next m
Else 'Print Selected
Month
.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & ComboBox2.Value & " " & TextBox2
.PrintPreview
' .PrintOut
End If
End With

End If
Me.Show

End Sub

.................................................. ........


"JLGWhiz" wrote in message
...
would you post the code you recorded?

"JB" wrote:

If I group sheets together and then make an entry on any one of the
grouped
sheets the entry is replicated throughout the grouped sheets.
I have done this whilst recording a macro and then (after stopping the
recording) I have cleared the cells
However if I run the macro the sheets are grouped together but the entry
only appears on the active sheet.
Why is this ?

Windows XP and Office 2003 Pro

vba problem when grouping sheets
 

I would assume your text is not being returned. Try this.

With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius, Bold _
Italic""&14" & Sheets("variables").Cells(m, 2).Value & "
" _
& TextBox2.Text
End With


"JB" wrote:

Perhaps I should have identified the line of code that doesn't work as I
expected .... it is:
...................................
With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius, Bold
Italic""&14" & Sheets("variables").Cells(m, 2) & " " & TextBox2
End With
...................................
TIA


"JB" wrote in message
. uk...
Thank you for your response,
I decided to start afresh before I read your reply and now I can Change
the value of a cell throughout the grouped worksheets but I am
experiencing the same problem with custom page headers for a group of
worksheets.

Here is the relevant code in the hope you can identify where I am going
wrong
Regards
.................................................. .............................................
Private Sub CommandButton1_Click()

If ComboBox1.ListIndex = -1 Then Exit Sub
If ComboBox2.ListIndex = -1 Then Exit Sub
If TextBox2 = "" Then Exit Sub
Me.Hide

If ComboBox1.ListIndex = 0 Then 'Print All Outlets

Dim Arr() As String 'Create an
Array of all Outlet Sheets
Dim N As Long

With ThisWorkbook.Worksheets
ReDim Arr(3 To .Count)
For N = 3 To .Count
Arr(N) = .Item(N).Name
Next N
End With
Sheets(Arr).Select

If ComboBox2.ListIndex = 0 Then 'Print All Months
For m = 3 To 14
With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius, Bold
Italic""&14" & Sheets("variables").Cells(m, 2) & " " & TextBox2
End With
Sheets(Arr).PrintPreview
' Sheets(Arr).PrintOut
Next m
Else 'Print Selected Month
ActiveSheet.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & ComboBox2.Value & " " & TextBox2
Sheets(Arr).PrintPreview
' Sheets(Arr).PrintOut
End If
Sheets(1).Activate

Else 'Print selected
Outlet
With Sheets(ComboBox1.Value)
If ComboBox2.ListIndex = 0 Then 'Print All Months
For m = 3 To 5 '14
.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & Sheets("variables").Cells(m, 2) & " " &
TextBox2
.PrintPreview
' .PrintOut
Next m
Else 'Print Selected
Month
.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & ComboBox2.Value & " " & TextBox2
.PrintPreview
' .PrintOut
End If
End With

End If
Me.Show

End Sub

.................................................. ........


"JLGWhiz" wrote in message
...
would you post the code you recorded?

"JB" wrote:

If I group sheets together and then make an entry on any one of the
grouped
sheets the entry is replicated throughout the grouped sheets.
I have done this whilst recording a macro and then (after stopping the
recording) I have cleared the cells
However if I run the macro the sheets are grouped together but the entry
only appears on the active sheet.
Why is this ?

Windows XP and Office 2003 Pro

vba problem when grouping sheets
 

Thanks for the suggestion but sadly it didn't improve matters.
The code updates the header of the first sheet in the group (the active
sheet) but it does not update the headers of the rest of the sheets in the
group.


"JLGWhiz" wrote in message
...
I would assume your text is not being returned. Try this.

With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius, Bold
_
Italic""&14" & Sheets("variables").Cells(m, 2).Value &
"
" _
& TextBox2.Text
End With


"JB" wrote:

Perhaps I should have identified the line of code that doesn't work as I
expected .... it is:
...................................
With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius,
Bold
Italic""&14" & Sheets("variables").Cells(m, 2) & " " & TextBox2
End With
...................................
TIA


"JB" wrote in message
. uk...
Thank you for your response,
I decided to start afresh before I read your reply and now I can Change
the value of a cell throughout the grouped worksheets but I am
experiencing the same problem with custom page headers for a group of
worksheets.

Here is the relevant code in the hope you can identify where I am going
wrong
Regards
.................................................. .............................................
Private Sub CommandButton1_Click()

If ComboBox1.ListIndex = -1 Then Exit Sub
If ComboBox2.ListIndex = -1 Then Exit Sub
If TextBox2 = "" Then Exit Sub
Me.Hide

If ComboBox1.ListIndex = 0 Then 'Print All
Outlets

Dim Arr() As String 'Create an
Array of all Outlet Sheets
Dim N As Long

With ThisWorkbook.Worksheets
ReDim Arr(3 To .Count)
For N = 3 To .Count
Arr(N) = .Item(N).Name
Next N
End With
Sheets(Arr).Select

If ComboBox2.ListIndex = 0 Then 'Print All Months
For m = 3 To 14
With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius,
Bold
Italic""&14" & Sheets("variables").Cells(m, 2) & " " & TextBox2
End With
Sheets(Arr).PrintPreview
' Sheets(Arr).PrintOut
Next m
Else 'Print Selected
Month
ActiveSheet.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & ComboBox2.Value & " " & TextBox2
Sheets(Arr).PrintPreview
' Sheets(Arr).PrintOut
End If
Sheets(1).Activate

Else 'Print selected
Outlet
With Sheets(ComboBox1.Value)
If ComboBox2.ListIndex = 0 Then 'Print All
Months
For m = 3 To 5 '14
.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & Sheets("variables").Cells(m, 2) & "
" &
TextBox2
.PrintPreview
' .PrintOut
Next m
Else 'Print Selected
Month
.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & ComboBox2.Value & " " & TextBox2
.PrintPreview
' .PrintOut
End If
End With

End If
Me.Show

End Sub

.................................................. ........


"JLGWhiz" wrote in message
...
would you post the code you recorded?

"JB" wrote:

If I group sheets together and then make an entry on any one of the
grouped
sheets the entry is replicated throughout the grouped sheets.
I have done this whilst recording a macro and then (after stopping
the
recording) I have cleared the cells
However if I run the macro the sheets are grouped together but the
entry
only appears on the active sheet.
Why is this ?

Windows XP and Office 2003 Pro

vba problem when grouping sheets
 

Well little by little we are identifying the problem.
Substitute this for the With statement and see if it does
the job.

For i = 3 To Sheets.Count
Sheets(i).PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius, Bold _
Italic""&14" & Sheets("variables").Cells(m, 2).Value _
& " " & TextBox2.Text
Next


"JB" wrote:

Thanks for the suggestion but sadly it didn't improve matters.
The code updates the header of the first sheet in the group (the active
sheet) but it does not update the headers of the rest of the sheets in the
group.


"JLGWhiz" wrote in message
...
I would assume your text is not being returned. Try this.

With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius, Bold
_
Italic""&14" & Sheets("variables").Cells(m, 2).Value &
"
" _
& TextBox2.Text
End With


"JB" wrote:

Perhaps I should have identified the line of code that doesn't work as I
expected .... it is:
...................................
With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius,
Bold
Italic""&14" & Sheets("variables").Cells(m, 2) & " " & TextBox2
End With
...................................
TIA


"JB" wrote in message
. uk...
Thank you for your response,
I decided to start afresh before I read your reply and now I can Change
the value of a cell throughout the grouped worksheets but I am
experiencing the same problem with custom page headers for a group of
worksheets.

Here is the relevant code in the hope you can identify where I am going
wrong
Regards
.................................................. .............................................
Private Sub CommandButton1_Click()

If ComboBox1.ListIndex = -1 Then Exit Sub
If ComboBox2.ListIndex = -1 Then Exit Sub
If TextBox2 = "" Then Exit Sub
Me.Hide

If ComboBox1.ListIndex = 0 Then 'Print All
Outlets

Dim Arr() As String 'Create an
Array of all Outlet Sheets
Dim N As Long

With ThisWorkbook.Worksheets
ReDim Arr(3 To .Count)
For N = 3 To .Count
Arr(N) = .Item(N).Name
Next N
End With
Sheets(Arr).Select

If ComboBox2.ListIndex = 0 Then 'Print All Months
For m = 3 To 14
With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius,
Bold
Italic""&14" & Sheets("variables").Cells(m, 2) & " " & TextBox2
End With
Sheets(Arr).PrintPreview
' Sheets(Arr).PrintOut
Next m
Else 'Print Selected
Month
ActiveSheet.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & ComboBox2.Value & " " & TextBox2
Sheets(Arr).PrintPreview
' Sheets(Arr).PrintOut
End If
Sheets(1).Activate

Else 'Print selected
Outlet
With Sheets(ComboBox1.Value)
If ComboBox2.ListIndex = 0 Then 'Print All
Months
For m = 3 To 5 '14
.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & Sheets("variables").Cells(m, 2) & "
" &
TextBox2
.PrintPreview
' .PrintOut
Next m
Else 'Print Selected
Month
.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & ComboBox2.Value & " " & TextBox2
.PrintPreview
' .PrintOut
End If
End With

End If
Me.Show

End Sub

.................................................. ........


"JLGWhiz" wrote in message
...
would you post the code you recorded?

"JB" wrote:

If I group sheets together and then make an entry on any one of the
grouped
sheets the entry is replicated throughout the grouped sheets.
I have done this whilst recording a macro and then (after stopping
the
recording) I have cleared the cells
However if I run the macro the sheets are grouped together but the
entry
only appears on the active sheet.
Why is this ?

Windows XP and Office 2003 Pro

vba problem when grouping sheets
 

Thanks, I have already used that approach of cycling through the sheets and
it does achieve the desired result but I thought that doing it by grouping
the sheets would be more elegant and perhaps a little slicker.

"JLGWhiz" wrote in message
...
Well little by little we are identifying the problem.
Substitute this for the With statement and see if it does
the job.

For i = 3 To Sheets.Count
Sheets(i).PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius, Bold _
Italic""&14" & Sheets("variables").Cells(m, 2).Value _
& " " & TextBox2.Text
Next


"JB" wrote:

Thanks for the suggestion but sadly it didn't improve matters.
The code updates the header of the first sheet in the group (the active
sheet) but it does not update the headers of the rest of the sheets in
the
group.


"JLGWhiz" wrote in message
...
I would assume your text is not being returned. Try this.

With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius,
Bold
_
Italic""&14" & Sheets("variables").Cells(m, 2).Value
&
"
" _
& TextBox2.Text
End With


"JB" wrote:

Perhaps I should have identified the line of code that doesn't work as
I
expected .... it is:
...................................
With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius,
Bold
Italic""&14" & Sheets("variables").Cells(m, 2) & " " & TextBox2
End With
...................................
TIA


"JB" wrote in message
. uk...
Thank you for your response,
I decided to start afresh before I read your reply and now I can
Change
the value of a cell throughout the grouped worksheets but I am
experiencing the same problem with custom page headers for a group
of
worksheets.

Here is the relevant code in the hope you can identify where I am
going
wrong
Regards
.................................................. .............................................
Private Sub CommandButton1_Click()

If ComboBox1.ListIndex = -1 Then Exit Sub
If ComboBox2.ListIndex = -1 Then Exit Sub
If TextBox2 = "" Then Exit Sub
Me.Hide

If ComboBox1.ListIndex = 0 Then 'Print All
Outlets

Dim Arr() As String 'Create
an
Array of all Outlet Sheets
Dim N As Long

With ThisWorkbook.Worksheets
ReDim Arr(3 To .Count)
For N = 3 To .Count
Arr(N) = .Item(N).Name
Next N
End With
Sheets(Arr).Select

If ComboBox2.ListIndex = 0 Then 'Print All Months
For m = 3 To 14
With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius,
Bold
Italic""&14" & Sheets("variables").Cells(m, 2) & " " & TextBox2
End With
Sheets(Arr).PrintPreview
' Sheets(Arr).PrintOut
Next m
Else 'Print Selected
Month
ActiveSheet.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & ComboBox2.Value & " " & TextBox2
Sheets(Arr).PrintPreview
' Sheets(Arr).PrintOut
End If
Sheets(1).Activate

Else 'Print
selected
Outlet
With Sheets(ComboBox1.Value)
If ComboBox2.ListIndex = 0 Then 'Print All
Months
For m = 3 To 5 '14
.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & Sheets("variables").Cells(m, 2) &
"
" &
TextBox2
.PrintPreview
' .PrintOut
Next m
Else 'Print
Selected
Month
.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & ComboBox2.Value & " " & TextBox2
.PrintPreview
' .PrintOut
End If
End With

End If
Me.Show

End Sub

.................................................. ........


"JLGWhiz" wrote in message
...
would you post the code you recorded?

"JB" wrote:

If I group sheets together and then make an entry on any one of
the
grouped
sheets the entry is replicated throughout the grouped sheets.
I have done this whilst recording a macro and then (after stopping
the
recording) I have cleared the cells
However if I run the macro the sheets are grouped together but the
entry
only appears on the active sheet.
Why is this ?

Windows XP and Office 2003 Pro

vba problem when grouping sheets
 

You will get an error message with that as written.
This should work better.

For i = 3 To Sheets.Count
Sheets(i).PageSetup.RightHeader = " " & Chr(10) & _
"&"Palladius, Bold Italic" &14" & _
Sheets("variables").Cells(m, 2).Value _
& " " & TextBox2.Text
Next


Everything starting with Sheets(i) thru TextBox2.Text
should be on one line.

"JB" wrote:

Thanks for the suggestion but sadly it didn't improve matters.
The code updates the header of the first sheet in the group (the active
sheet) but it does not update the headers of the rest of the sheets in the
group.


"JLGWhiz" wrote in message
...
I would assume your text is not being returned. Try this.

With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius, Bold
_
Italic""&14" & Sheets("variables").Cells(m, 2).Value &
"
" _
& TextBox2.Text
End With


"JB" wrote:

Perhaps I should have identified the line of code that doesn't work as I
expected .... it is:
...................................
With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius,
Bold
Italic""&14" & Sheets("variables").Cells(m, 2) & " " & TextBox2
End With
...................................
TIA


"JB" wrote in message
. uk...
Thank you for your response,
I decided to start afresh before I read your reply and now I can Change
the value of a cell throughout the grouped worksheets but I am
experiencing the same problem with custom page headers for a group of
worksheets.

Here is the relevant code in the hope you can identify where I am going
wrong
Regards
.................................................. .............................................
Private Sub CommandButton1_Click()

If ComboBox1.ListIndex = -1 Then Exit Sub
If ComboBox2.ListIndex = -1 Then Exit Sub
If TextBox2 = "" Then Exit Sub
Me.Hide

If ComboBox1.ListIndex = 0 Then 'Print All
Outlets

Dim Arr() As String 'Create an
Array of all Outlet Sheets
Dim N As Long

With ThisWorkbook.Worksheets
ReDim Arr(3 To .Count)
For N = 3 To .Count
Arr(N) = .Item(N).Name
Next N
End With
Sheets(Arr).Select

If ComboBox2.ListIndex = 0 Then 'Print All Months
For m = 3 To 14
With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius,
Bold
Italic""&14" & Sheets("variables").Cells(m, 2) & " " & TextBox2
End With
Sheets(Arr).PrintPreview
' Sheets(Arr).PrintOut
Next m
Else 'Print Selected
Month
ActiveSheet.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & ComboBox2.Value & " " & TextBox2
Sheets(Arr).PrintPreview
' Sheets(Arr).PrintOut
End If
Sheets(1).Activate

Else 'Print selected
Outlet
With Sheets(ComboBox1.Value)
If ComboBox2.ListIndex = 0 Then 'Print All
Months
For m = 3 To 5 '14
.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & Sheets("variables").Cells(m, 2) & "
" &
TextBox2
.PrintPreview
' .PrintOut
Next m
Else 'Print Selected
Month
.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & ComboBox2.Value & " " & TextBox2
.PrintPreview
' .PrintOut
End If
End With

End If
Me.Show

End Sub

.................................................. ........


"JLGWhiz" wrote in message
...
would you post the code you recorded?

"JB" wrote:

If I group sheets together and then make an entry on any one of the
grouped
sheets the entry is replicated throughout the grouped sheets.
I have done this whilst recording a macro and then (after stopping
the
recording) I have cleared the cells
However if I run the macro the sheets are grouped together but the
entry
only appears on the active sheet.
Why is this ?

Windows XP and Office 2003 Pro

vba problem when grouping sheets
 

Thanks for your help and I'll use that approach. It is noticably slower than
making changes to a group of sheets and annoying when it works when
recording a macro but not when re-running the generated code.
Many thanks again for your help


"JLGWhiz" wrote in message
...
You will get an error message with that as written.
This should work better.

For i = 3 To Sheets.Count
Sheets(i).PageSetup.RightHeader = " " & Chr(10) & _
"&"Palladius, Bold Italic" &14" & _
Sheets("variables").Cells(m, 2).Value _
& " " & TextBox2.Text
Next


Everything starting with Sheets(i) thru TextBox2.Text
should be on one line.

"JB" wrote:

Thanks for the suggestion but sadly it didn't improve matters.
The code updates the header of the first sheet in the group (the active
sheet) but it does not update the headers of the rest of the sheets in
the
group.


"JLGWhiz" wrote in message
...
I would assume your text is not being returned. Try this.

With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius,
Bold
_
Italic""&14" & Sheets("variables").Cells(m, 2).Value
&
"
" _
& TextBox2.Text
End With


"JB" wrote:

Perhaps I should have identified the line of code that doesn't work as
I
expected .... it is:
...................................
With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius,
Bold
Italic""&14" & Sheets("variables").Cells(m, 2) & " " & TextBox2
End With
...................................
TIA


"JB" wrote in message
. uk...
Thank you for your response,
I decided to start afresh before I read your reply and now I can
Change
the value of a cell throughout the grouped worksheets but I am
experiencing the same problem with custom page headers for a group
of
worksheets.

Here is the relevant code in the hope you can identify where I am
going
wrong
Regards
.................................................. .............................................
Private Sub CommandButton1_Click()

If ComboBox1.ListIndex = -1 Then Exit Sub
If ComboBox2.ListIndex = -1 Then Exit Sub
If TextBox2 = "" Then Exit Sub
Me.Hide

If ComboBox1.ListIndex = 0 Then 'Print All
Outlets

Dim Arr() As String 'Create
an
Array of all Outlet Sheets
Dim N As Long

With ThisWorkbook.Worksheets
ReDim Arr(3 To .Count)
For N = 3 To .Count
Arr(N) = .Item(N).Name
Next N
End With
Sheets(Arr).Select

If ComboBox2.ListIndex = 0 Then 'Print All Months
For m = 3 To 14
With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius,
Bold
Italic""&14" & Sheets("variables").Cells(m, 2) & " " & TextBox2
End With
Sheets(Arr).PrintPreview
' Sheets(Arr).PrintOut
Next m
Else 'Print Selected
Month
ActiveSheet.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & ComboBox2.Value & " " & TextBox2
Sheets(Arr).PrintPreview
' Sheets(Arr).PrintOut
End If
Sheets(1).Activate

Else 'Print
selected
Outlet
With Sheets(ComboBox1.Value)
If ComboBox2.ListIndex = 0 Then 'Print All
Months
For m = 3 To 5 '14
.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & Sheets("variables").Cells(m, 2) &
"
" &
TextBox2
.PrintPreview
' .PrintOut
Next m
Else 'Print
Selected
Month
.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & ComboBox2.Value & " " & TextBox2
.PrintPreview
' .PrintOut
End If
End With

End If
Me.Show

End Sub

.................................................. ........


"JLGWhiz" wrote in message
...
would you post the code you recorded?

"JB" wrote:

If I group sheets together and then make an entry on any one of
the
grouped
sheets the entry is replicated throughout the grouped sheets.
I have done this whilst recording a macro and then (after stopping
the
recording) I have cleared the cells
However if I run the macro the sheets are grouped together but the
entry
only appears on the active sheet.
Why is this ?

Windows XP and Office 2003 Pro

vba problem when grouping sheets
 

Unfortunately, not every feature of Excel is directly translatable to VBA and
vice versa. But there is always another way to skin the cat.

"JB" wrote:

Thanks for your help and I'll use that approach. It is noticably slower than
making changes to a group of sheets and annoying when it works when
recording a macro but not when re-running the generated code.
Many thanks again for your help


"JLGWhiz" wrote in message
...
You will get an error message with that as written.
This should work better.

For i = 3 To Sheets.Count
Sheets(i).PageSetup.RightHeader = " " & Chr(10) & _
"&"Palladius, Bold Italic" &14" & _
Sheets("variables").Cells(m, 2).Value _
& " " & TextBox2.Text
Next


Everything starting with Sheets(i) thru TextBox2.Text
should be on one line.

"JB" wrote:

Thanks for the suggestion but sadly it didn't improve matters.
The code updates the header of the first sheet in the group (the active
sheet) but it does not update the headers of the rest of the sheets in
the
group.


"JLGWhiz" wrote in message
...
I would assume your text is not being returned. Try this.

With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius,
Bold
_
Italic""&14" & Sheets("variables").Cells(m, 2).Value
&
"
" _
& TextBox2.Text
End With


"JB" wrote:

Perhaps I should have identified the line of code that doesn't work as
I
expected .... it is:
...................................
With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius,
Bold
Italic""&14" & Sheets("variables").Cells(m, 2) & " " & TextBox2
End With
...................................
TIA


"JB" wrote in message
. uk...
Thank you for your response,
I decided to start afresh before I read your reply and now I can
Change
the value of a cell throughout the grouped worksheets but I am
experiencing the same problem with custom page headers for a group
of
worksheets.

Here is the relevant code in the hope you can identify where I am
going
wrong
Regards
.................................................. .............................................
Private Sub CommandButton1_Click()

If ComboBox1.ListIndex = -1 Then Exit Sub
If ComboBox2.ListIndex = -1 Then Exit Sub
If TextBox2 = "" Then Exit Sub
Me.Hide

If ComboBox1.ListIndex = 0 Then 'Print All
Outlets

Dim Arr() As String 'Create
an
Array of all Outlet Sheets
Dim N As Long

With ThisWorkbook.Worksheets
ReDim Arr(3 To .Count)
For N = 3 To .Count
Arr(N) = .Item(N).Name
Next N
End With
Sheets(Arr).Select

If ComboBox2.ListIndex = 0 Then 'Print All Months
For m = 3 To 14
With ActiveSheet.PageSetup
.RightHeader = " " & Chr(10) & "&""Palladius,
Bold
Italic""&14" & Sheets("variables").Cells(m, 2) & " " & TextBox2
End With
Sheets(Arr).PrintPreview
' Sheets(Arr).PrintOut
Next m
Else 'Print Selected
Month
ActiveSheet.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & ComboBox2.Value & " " & TextBox2
Sheets(Arr).PrintPreview
' Sheets(Arr).PrintOut
End If
Sheets(1).Activate

Else 'Print
selected
Outlet
With Sheets(ComboBox1.Value)
If ComboBox2.ListIndex = 0 Then 'Print All
Months
For m = 3 To 5 '14
.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & Sheets("variables").Cells(m, 2) &
"
" &
TextBox2
.PrintPreview
' .PrintOut
Next m
Else 'Print
Selected
Month
.PageSetup.RightHeader = " " & Chr(10) &
"&""Palladius, Bold Italic""&14" & ComboBox2.Value & " " & TextBox2
.PrintPreview
' .PrintOut
End If
End With

End If
Me.Show

End Sub

.................................................. ........


"JLGWhiz" wrote in message
...
would you post the code you recorded?

"JB" wrote:

If I group sheets together and then make an entry on any one of
the
grouped
sheets the entry is replicated throughout the grouped sheets.
I have done this whilst recording a macro and then (after stopping
the
recording) I have cleared the cells
However if I run the macro the sheets are grouped together but the
entry
only appears on the active sheet.
Why is this ?

Windows XP and Office 2003 Pro