We help IT Professionals succeed at work.
Get Started

Split data  in separate sheets - after changing the column from D to A macro has a problem

Last Modified: 2017-03-10
Thanks goes to Subodh Tiwari (Neeraj). How I can modify the column range? If I change the column in the code from D to A the macro does not work correct. If I use as coded below it works great. What I am doing wrong? Which other parameters I have to change?

Thanks Regards

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, "/", "-"))
    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.Range("A1").CurrentRegion.Borders.Color = vbBlack
        Set WS = Nothing
    End With
Next it
dataWS.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

Open in new window

Watch Question
Top Expert 2016
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

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.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE