Creat worksheets as per the list

[Balajibenz]

Hi,

I have a workbook where it got the the list as below in column B of sheet1. Can someone help me with VBA code to create each worksheet(in the same workbook) for each manager in the below list and worksheet name should be same as manager name. the list might change so whenever I run the code it should create new worksheets. thank you in advance.

Manager
Max
Alex
Harry
Sam
Sid

 

[mohadin]

Hi
What about

Sub test()
    Dim a As Variant
    Dim i As Long
    Dim ws As Worksheet
    a = Cells(2, 2).Resize(Cells(Rows.Count, 2).End(xlUp).Row - 2)
    For i = 1 To UBound(a)
        Set ws = ThisWorkbook.Sheets.Add(After:= _
                                         ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        ws.Name = a(i, 1)
    Next
End Sub

 

[Balajibenz]

Hi
What about

Sub test()
    Dim a As Variant
    Dim i As Long
    Dim ws As Worksheet
    a = Cells(2, 2).Resize(Cells(Rows.Count, 2).End(xlUp).Row - 2)
    For i = 1 To UBound(a)
        Set ws = ThisWorkbook.Sheets.Add(After:= _
                                         ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        ws.Name = a(i, 1)
    Next
End Sub

Hi Mate,

that works perfectly. can you also help me with the add on to the above request if you can please.

I have a sheet named as "Source" where there will be data. I want to insert a pivot table (in"A2") in each of the worksheets created above and source for pivot table will be the data in "Source" worksheet which will also have column named as "Manager" which needs to be added to the filters of the pivot table and the respective manager name should be filtered in that field. I can able to add other pivot fields that are required. Thanks again mate.