Create worksheet for All Possible combination in the same row

[shelim481]

Hi, how would i go about creating worksheets for every possible combination of data. but the data is in the same row, only separated by by one blank row... there are significantly more then shown below..

x1
x2
x3

y1
y2

the tabs (new worksheet) should be created is
x1-y1
x2-y1
x3-y1
x1-y2



thanks
x2-y2
x3-y2

 

[Yongle]

Test with EXACTLY the same layout as in my example (which is based on your data)
Run from the same sheet
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]x1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]x2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]x3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]y1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]y2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: XX[/td][/tr][/table]

Sub InsertSheets()
    Dim rng1 As Range, rng2 As Range, c1 As Range, c2 As Range

    Set rng1 = Range("A2", Range("A2").End(xlDown))
    Set rng2 = Range("A" & Rows.Count).End(xlUp)
    Set rng2 = Range(rng2, rng2.End(xlUp))
   
    For Each c1 In rng1
        For Each c2 In rng2
            On Error Resume Next
                ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
                WorkSheets(WorkSheets.Count).Name = c1 & "-" & c2
        Next c2
    Next c1
End Sub