Compile Error : Expected Function or variable while replicating grouping format

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:

orEach ws.SelectIn ActiveWorkbook.SheetsOverall code:

Dim sh AsVariant
Dim ws As Worksheet
Dim j AsLong
Dim i AsLong
Dim lastRow AsLong
Dim lastcolumn AsLong
Dim sht AsString
Dim source As Worksheet
Dim z AsLong
PrivateSub CommandButton1_Click()
IfMe.OptionButton1.Value =TrueThen
For i =0ToMe.ListBox1.ListCount -1
IfMe.ListBox1.Selected(i)Then
Set source = Worksheets(Me.ListBox1.List(i))
lastRow = source.UsedRange.Row + source.UsedRange.Rows.Count -1
For z =0ToMe.ListBox2.ListCount -1
IfMe.ListBox2.Selected(z)Then
Set ws = ActiveWorkbook.Worksheets(Me.ListBox2.List(z))
ForEach ws.SelectIn ActiveWorkbook.Sheets
For j = source.UsedRange.Row To lastRow
ws.Rows(j).OutlineLevel = source.Rows(j).OutlineLevel
Next j
Next ws
EndIf
Next
EndIf
Next
EndIf
EndSub
PrivateSub OptionButton1_Click()
Me.ListBox2.MultiSelect = fmMultiSelectExtended
EndSub
PrivateSub UserForm_Initialize()
'for each loop the add visible sheets
ForEach sh In ActiveWorkbook.Sheets
'add sheets to the listbox
Me.ListBox1.AddItem sh.Name
Next sh
'for each loop the add visible sheets
ForEach sh In ActiveWorkbook.Sheets
'add sheets to the listbox
Me.ListBox2.AddItem sh.Name
Next sh
End Sub
Participant Asked on May 16, 2017 in VBA: User Forms.
Add Comment
0 Answer(s)
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.