How to create separate Sheets workbook with name

[Nitesh Khot]

Hi ..,

Attached file contains data where I want to create seprate sheets based on Location with Name.

For E.g Sheet1 contains data from A1:D19

I want to create sheets with data based on location name location name is in column D.

anyone have idea..!

 

[Asheesh]

Hi Nitesh

See the attached..

Modified the source code a lil: http://www.extendoffice.com/documen...into-multiple-worksheets-based-on-column.html

 

[Marc L]

Hi !​

anyone have idea

With this basic simple need, just by using a filter with few clicks,
no ever need a code !

Anyway, according from attachment an Excel basics advanced filter way :​

Sub Demo()
    Dim Rg As Range
With Application:  .ScreenUpdating = False:  R& = .SheetsInNewWorkbook:  End With
    Set Rg = Sheet1.UsedRange
        Rg.Columns(4).AdvancedFilter xlFilterCopy, , Rg(1, 11), True
   With Rg(1, 11).CurrentRegion
        .Sort .Cells(1), xlAscending, Header:=xlYes
                Application.SheetsInNewWorkbook = .Rows.Count - 1
        With Workbooks.Add
                Application.SheetsInNewWorkbook = R
            For R = 1 To .Worksheets.Count
                With .Worksheets(R)
                    .Name = Sheet1.Cells(R + 1, 11).Value
                    Sheet1.[K2].Value = .Name
                    Rg.AdvancedFilter xlFilterCopy, Sheet1.[K1:K2], .Cells(1)
                    .UsedRange.Columns(1).AutoFit
                End With
            Next
        End With
        .Clear
   End With
            Application.ScreenUpdating = True
    Set Rg = Nothing
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Nitesh Khot]

Thanks Like Sir..

Can you just put coding comment in above code so easily understand this type of advance code...as i have never seen this type of code..

Thanks in advance...

 

[Marc L]

I suppose you related Asheesh's code 'cause mine comes from Excel basics !

Easy to understand : place text cursor on a statement,

hit

key and read VBA inner help ! No difficulty, all at beginner level …

Code just uses an advanced filter as you can manually directly operate !
​