Sub SplitDataBasedOnColumnD() Dim dataWS As Worksheet, WS As Worksheet Dim dict, x Dim i As Long, lr As Long Application.ScreenUpdating = False Set dataWS = Sheets("Database") lr = dataWS.Cells(Rows.Count, 1).End(xlUp).Row x = dataWS.Range("D2:D" & lr).Value Set dict = CreateObject("Scripting.Dictionary") For i = 1 To UBound(x, 1) If x(i, 1) <> "" Then dict.Item(x(i, 1)) = "" End If Next i If dict.Count = 0 Then MsgBox "No data found in column D.", vbExclamation, "Data Not Found!" Exit Sub End If dataWS.AutoFilterMode = False For Each it In dict.keys On Error Resume Next Set WS = Sheets(Replace(it, "/", "-")) WS.Cells.Clear On Error GoTo 0 If WS Is Nothing Then Sheets.Add(after:=Sheets(Sheets.Count)).Name = Replace(it, "/", "-") Set WS = ActiveSheet End If With dataWS.Rows(1) .AutoFilter field:=4, Criteria1:=it dataWS.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy WS.Range("A1") WS.Columns.AutoFit WS.Range("A1").CurrentRegion.Borders.Color = vbBlack Set WS = Nothing End With Next it dataWS.AutoFilterMode = False dataWS.Activate Application.ScreenUpdating = True End Sub
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE