vba problem when grouping sheets

[JB]

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

 

[JLGWhiz]

would you post the code you recorded?

 

[JB]

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

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

 

[JB]

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

 

[JLGWhiz]

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]

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]

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]

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]

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]

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]

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