Split by Legal Employer as Separate Workbooks shows blank values

[fareedexcel]

I need your help to sort out in getting the right data in each workbooks. As per the below macro code, the workbook is getting split as per the legal employer name correctly. But no data gets populated in each workbook. Please help in fixing the error.

Option Explicit

Sub Split_Data_in_workbooks()

Application.ScreenUpdating = False


Dim data_sh As Worksheet
Set data_sh = ThisWorkbook.Sheets("Data")

Dim setting_Sh As Worksheet
Set setting_Sh = ThisWorkbook.Sheets("Settings")

Dim nwb As Workbook
Dim nsh As Worksheet

''''' Get unique Entity

setting_Sh.Range("A:A").Clear
data_sh.AutoFilterMode = False
data_sh.Range("I:I").Copy setting_Sh.Range("A1")

setting_Sh.Range("A:A").RemoveDuplicates 1, xlYes

Dim i As Integer

For i = 2 To Application.CountA(setting_Sh.Range("A:A"))
    
    data_sh.UsedRange.AutoFilter 2, setting_Sh.Range("A" & i).Value
    
    
    Set nwb = Workbooks.Add
    Set nsh = nwb.Sheets(1)
    
    data_sh.UsedRange.SpecialCells(xlCellTypeVisible).Copy nsh.Range("A1")
    nsh.UsedRange.EntireColumn.ColumnWidth = 15
    
    nwb.SaveAs setting_Sh.Range("H6").Value & "/" & setting_Sh.Range("A" & i).Value & ".xlsx"
    nwb.Close False
    data_sh.AutoFilterMode = False
Next i

setting_Sh.Range("A:A").Clear

MsgBox "Done"


End Sub

 

[Marc L]

Bad logic !​

​

Why filtering names on column #2 which is not the column where are names ?!​

As per your code so just take the same column when creating the column A of Settings worksheet …​

​

It's easier to avoid this kind of error using an advanced filter rather than a filter.​

​

 

[p45cal]

ie:
data_sh.UsedRange.AutoFilter 9, setting_Sh.Range("A" & i).Value