(Solved) – How can I apply a for each ws loop within this array split?


Say I have a main wb with 2 sheets: orders/returns. Each sheet has the same amount of columns (A-Z) and the item values are in column 10. Now I need to create a workbook for each item down the const Item variable. Simple enough, but I also need to have this apply to both sheets. Lets say the first group of rows falls under the item Apples, I need code to: split 1st ws item data -> move to template 1st ws -> split 2nd ws item data -> move to template 2nd ws -> loop until each item has their own new wb

Here is a very crude script I have together to get started, but I am a little overwhelmed by the task at hand, is this even possible?

Sub Item_Split()
    Dim Wb As Workbook
    Dim Data
    Dim srcRow As Long, destRow As Long, srcCol As Long, destCol As Long
    Dim Dest As Range
    Dim Template As String

    Template = "M:Template.xlsx"
    Set Wb = Workbooks.Open(Filename:=Template)
    Set Dest = Wb.Sheets("Detail").Range("A3")

        'Data table is same amount of columns and Item variable always in 10th column
        Data = activeworksheet.Range("Z2", .Range("A" & Rows.Count).End(xlUp))


    Dim ws As Worksheet: Set ws = activeworksheet
    Dim Item
    Const Item = 10

    For Each ws In ThisWorkbook.Worksheets
        For srcRow = 1 To UBound(Data)
            If Data(srcRow, 10) <> Item Then
                If srcRow > 1 Then
                    Wb.SaveCopyAs ThisWorkbook.Path & Application.PathSeparator & _
                    ValidFileName(Item & ".xlsx")
                End If
                With Wb.ActiveSheet
                    .Rows(3 & ":" & .Rows.Count).ClearContents
                End With
                destRow = 0
            End If
            destCol = 0
            For srcCol = 1 To UBound(Data, 2)
                'need a way to place source ws data split to appropriate workbook.add sheet
                Dest.Offset(destRow, destCol) = Data(srcRow, srcCol)
                destCol = destCol   1
            destRow = destRow   1
    Next ws
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *