| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Grouping sheets copies Header across to all sheets | Excel Worksheet Functions | |||
| grouping sheets | Excel Programming | |||
| Grouping Sheets | Excel Discussion (Misc queries) | |||
| grouping sheets | Excel Programming | |||
| Grouping Sheets in VBA | Excel Programming | |||
![JB[_5_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
