Need a Macro with Loop to Insert new Worksheet

[Farisha]

<TABLE class=tborder id=post2776275 cellSpacing=0 cellPadding=6 width="100%" align=center border=0><TBODY><TR vAlign=top><TD class=alt1 id=td_post_2776275 style="BORDER-RIGHT: #ffffff 1px solid">I need to insert 10 new sheets in a workbook, base on the value in Column B. I want to get it in a way that whenever the value of Column B change, it insert the new sheet and rename the Sheet Tilte same as Value in Column B. Loop will work as I have some values in Column in Duplicate. Please see below:

Column B

Accounting
HR
Engineering
HR
Engineering
Buisness
Purchase
Purchase
Acounting



Thanks for the help.
<!-- / message --></TD></TR><TR><TD class=alt2 style="BORDER-RIGHT: #ffffff 1px solid; BORDER-TOP: #ffffff 0px solid; BORDER-LEFT: #ffffff 1px solid; BORDER-BOTTOM: #ffffff 1px solid"> </TD><TD class=alt1 style="BORDER-RIGHT: #ffffff 1px solid; BORDER-TOP: #ffffff 0px solid; BORDER-LEFT: #ffffff 0px solid; BORDER-BOTTOM: #ffffff 1px solid" align=right><!-- controls --></TD></TR></TBODY></TABLE>

 

[kpark91]

Hi Farisha. Before I start coding,
in your example, how many worksheets would there be?
8 or 4?

If new worksheet is required to be added everytime the value changes then there would be some issues later on as there will be duplicate names present with numbering system at the end of the name. "Engineering", "Engineering(1)"

 

[Farisha]

3 Sheets. Sheet 1 has the data.

Thanks

 

[Farisha]

3 Sheets. Sheet 1 has the data.

The data related to Engineering (Column C & D etc. ) will change or added every week and there would be only one workbook each week.

I got one updated spreadsheet each week and need to create report for each department. So in one week may be there are 2 rows for Engineering, and 2nd week it is 5. I keep that workbook as a back up sheet. and Every week use the new workbook.

Hope it will give you a more clear picture.

Thanks for your quick reply.

 

[kpark91]

Hi, so you just want worksheets which are acting as categories.
is that correct?

So, in your example,
it should give
"Accounting"
"HR"
"Engineering"
"Business"
"Purchase"

which are 5 sheets... Isn't it?
I do not see how there can only be 3 sheets..

 

[kpark91]

Anyways try this
This code should be pasted onto your worksheet where column B's are filled in with the worksheet names.

Sub createUniqueWS()
    Dim LR&, i&, j&, sw As Boolean
    LR = Range("B" & Rows.count).End(xlUp).Row
    For i = 1 To LR
        sw = False
        
        For j = 1 To Sheets.count
            If ThisWorkbook.Sheets(j).Name = Range("B" & i).Value Then sw = True
        Next j
        
        If sw = False Then
            Worksheets.Add(After:=Worksheets(Worksheets.count)).Name = Range("B" & i).Text
        End If
    Next i
End Sub

 

[Farisha]

Thanks, I will get back to you if need more help.

Thanks again