Compile Error : Expected Function or variable while replicating grouping format

[itsme2216]

I have created a userform which has two Listboxes (Listbox1) & (ListBox2) which list down all the worksheets name present in the workbook. ListBox1 is my source sheet and Listbox2 are my Target sheets I have an OptionBox (OptionBox1).
Basically I want that when I select a sheet from Listbox1 and select few sheets from Listbox 2 and if my OptionBox1 is true then the grouping rows format of the source sheet(Listbox1) should replicate to the target sheets (Listbox2) selected.


I am getting compile error in the below line:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;"> For Each ws.Select In ActiveWorkbook.Sheets</code>Overall code:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">Dim sh As Variant
Dim ws As Worksheet
Dim j As Long
Dim i As Long
Dim lastRow As Long
Dim lastcolumn As Long
Dim sht As String
Dim source As Worksheet
Dim z As Long

Private Sub CommandButton1_Click()
If Me.OptionButton1.Value = True Then

For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
Set source = Worksheets(Me.ListBox1.List(i))
lastRow = source.UsedRange.Row + source.UsedRange.Rows.Count - 1
For z = 0 To Me.ListBox2.ListCount - 1
If Me.ListBox2.Selected(z) Then
Set ws = ActiveWorkbook.Worksheets(Me.ListBox2.List(z))
For Each ws.Select In ActiveWorkbook.Sheets
For j = source.UsedRange.Row To lastRow
ws.Rows(j).OutlineLevel = source.Rows(j).OutlineLevel
Next j
Next ws
End If
Next
End If
Next
End If
End Sub

Private Sub OptionButton1_Click()

Me.ListBox2.MultiSelect = fmMultiSelectExtended
End Sub

Private Sub UserForm_Initialize()
'for each loop the add visible sheets
For Each sh In ActiveWorkbook.Sheets

'add sheets to the listbox

Me.ListBox1.AddItem sh.Name

Next sh

'for each loop the add visible sheets
For Each sh In ActiveWorkbook.Sheets

'add sheets to the listbox

Me.ListBox2.AddItem sh.Name

Next sh

End Sub</code>

 

[Norie]

Try this.

For Each ws In ActiveWorkbook.Sheets

 

[itsme2216]

Try this.

For Each ws In ActiveWorkbook.Sheets

By using this line its only making changes to the activesheet not on all the selected sheets which I have selected in the ListBox2.
Please suggest how should I modify this line so that whatever sheets I have selected in ListBox2 it makes changes to those selected sheets only.

 

[Norie]

You don't need that loop, you've already got code that goes through ListBox2 and checks what's been selected, here it is.

For z =0 To Me.ListBox2.ListCount -1
    If Me.ListBox2.Selected(z) Then
        Set ws = ActiveWorkbook.Worksheets(Me.ListBox2.List(z))
        ' add code here to work with worksheet that ws refers to
    End If
Next z

You need to add code where I've indicated to do whatever it is you want to do to the selected worksheet(s).

 

[itsme2216]

Its working thank you so much.....