Q: For each column create unique workbook I have an excel sheet and I want each column to be stored in different workbook.
Zone    State
North   Punjab
North   Haryana
South   Kerala
South   Chennai

O/p : should be two excel workbook with zone in one book and State in another
With this code I am able to copy content to new sheet in existing workbook but not in new workbook.
Sub CopyUnique()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Main")
    Set s2 = Sheets("Count")
    s1.Range("B:B").Copy s2.Range("A1")
    s2.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
End Sub


A: See Below Code:
Sub CopyUnique()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Main")
    Set s2 = Sheets("Count")
    s1.Range("B:B").Copy s2.Range("A1")
    s2.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo


    s2.Move

    ActiveWorkbook.SaveAs ("C:\--------\Desktop\Test\New.xlsx")

    'If you want close that workbook
    ActiveWorkbook.Close True

End Sub

