creat new tabs tabs

Can an Expert provide me with VBA code that will create a new tab for any items ic column J where the date in column B is Today.

There will be mutiple items in 'J' with the same name but I only want one tab per name.

i.e.

Date      Ref
01/08/2014      CLOVC
01/08/2014      CLOVC
01/08/2014      CLOVC
01/08/2014      BKIDQ
02/08/2014      BKIDQ
02/08/2014      CLOVC
01/08/2014      SUUKG
02/08/2014      MSSU0

So I would tabs created for CLOVC, BKIDQ, SUUKG

Thanks in advance
JagwarmanAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
Amended code

Sub macro()

For Each c In Range(Range("J2"), Range("J" & Rows.Count).End(xlUp))
    If c.Offset(0, -8) = Date Then
        On Error Resume Next
        Set Sh = Nothing
        Set Sh = ActiveWorkbook.Worksheets(c.Value)
        On Error GoTo 0
        If Sh Is Nothing Then
            ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
            Worksheets(Worksheets.Count).Name = c.Value
        End If
    End If
Next
End Sub
0
 
Rgonzo1971Commented:
Hi,

pls try

Sub macro()

For Each c In Range(Range("J2"), Range("J" & Rows.Count).End(xlUp))
    If c.Offset(0, -9) = Date Then
        On Error Resume Next
        Set Sh = Nothing
        Set Sh = ActiveWorkbook.Worksheets(c.Value)
        On Error GoTo 0
        If Sh Is Nothing Then
            ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
            Worksheets(Worksheets.Count).Name = c.Value
        End If
    End If
Next
End Sub

Open in new window

Regards
0
 
JagwarmanAuthor Commented:
Hi Rgonzo1971

code runs from start to end but no sheets are being created.

When I step through code it looks ok but no tabs????
0
 
JagwarmanAuthor Commented:
Brilliant thanks
0
 
JagwarmanAuthor Commented:
I have a related question which I am now going to post which is for each tab created I need to put its relative data onto that tab If I had thought about it earlier maybe it could have been one post but this way you get more points :-)   if you respond
0
All Courses

From novice to tech pro — start learning today.