Loop through columns, create a workbook and sheets.

[JonesZoid]

I have a list of sitenames in column E and a list of racks in column C which are in the said sitename.

A	B	C	D	E
		Rack A		Site 1
		Rack A		Site 2
		Rack B		Site 2
		Rack C		Site 2
		Rack A		Site 3
		Rack B		Site 3

I would like to loop through column E and create a workbook for that site, the range will be in alphabetical order. Then use column C to create a worksheet in each workbook for each rack.

I can do all the data manipulation and transferring, just not sure how to do the above.

Any help would be greatly appreciated.

 

[mumps]

This macro assumes you have headers in row 1. Change the sheet name (in red) to suit your needs.

Sub CreateWorkbooks()
    Application.ScreenUpdating = False
    Dim rack As Range, v As Variant, i As Long, srcWS As Worksheet, LastRow As Long
    Set srcWS = ThisWorkbook.Sheets("Sheet1")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = srcWS.Range("E2:E" & LastRow).Value
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(v, 1)
            If Not .Exists(v(i, 1)) Then
                .Add v(i, 1), Nothing
                srcWS.Range("E1:E" & LastRow).AutoFilter 1, v(i, 1)
                Workbooks.Add 1
                For Each rack In srcWS.Range("C2:C" & LastRow).SpecialCells(xlCellTypeVisible)
                    Sheets.Add(After:=Sheets(Sheets.Count)).Name = rack
                Next rack
            End If
        Next i
    End With
    srcWS.Range("E2").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[JonesZoid]

Thanks Mumps that nearly worked perfectly, had to adjust the autofilter to match column E, as you had it set to column A.

Otherwise great job, thanks again.

 

[mumps]

You are very welcome.