CODEWITHSUNDEEP
vbaexcel
Curious Lad
Curious Lad

Reputation: 447

Creating worksheets based on unique values

I have a data set sorted date wise and I want to copy the data based on month, i.e, data of each month to be copied to a new worksheet and the name of sheet would be the name of month present. Data set: http://bit.ly/1CoHV5j

I tried running the following code: 

Sub x()
Dim rng As Range

With ActiveSheet
    .AutoFilterMode = False
    Sheets.Add().Name = "Temp"
    .Range("H2", .Range("H2").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Temp").Range("A1"), Unique:=True

    For Each rng In Sheets("Temp").UsedRange.Offset(1).Resize(Sheets("Temp").UsedRange.Rows.Count - 1)
        .Range("A1").CurrentRegion.AutoFilter field:=8, Criteria1:=rng
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = rng.Text
        .AutoFilter.Range.Copy Sheets(rng).Range("A1")

    Next rng

    .AutoFilterMode = False
    Application.DisplayAlerts = False
    Sheets("Temp").Delete
    Application.DisplayAlerts = True

End With

End Sub

But this error keeps coming up:

"You typed an invalid name for a sheet or chart. Make sure that: The name that you type does not exceed 31 charaters The name does not contain any of thefollowing characters: \ / ? * [ or ] You do not leave the name blank

Kindly help and tell me where I am going wrong.

Upvotes: 1

Views: 101

Answers (1)

Krishna
Krishna

Reputation: 2481

the text in some of the cells may have reserved characters... can you try the below, replace the sheeets.add bit with cleaned up string

PS: You should also make sure the cell you are using as a name is not empty

Full Code should look like something below

Sub x()
    Dim rng As Range
    Dim SheeetName as string    
    With ActiveSheet
    .AutoFilterMode = False
    Sheets.Add().Name = "Temp"
    .Range("H2", .Range("H2").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Temp").Range("A1"), Unique:=True
    For Each rng In Sheets("Temp").UsedRange.Offset(1).Resize(Sheets("Temp").UsedRange.Rows.Count - 1)
        .Range("A1").CurrentRegion.AutoFilter field:=8, Criteria1:=rng
        SheeetName = GetGoodSheetName(rng.Text)
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = SheeetName
        .AutoFilter.Range.Copy Sheets(SheetName).Range("A1")

    Next rng

    .AutoFilterMode = False
    Application.DisplayAlerts = False
    Sheets("Temp").Delete
    Application.DisplayAlerts = True
    End With
End Sub

Function GetGoodSheetName(fromName As String) As String
    Dim objRegex As Object
    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
        .Global = True
        .Pattern = "[\<\>\*\\\/\?|]"
        GetGoodSheetName = .Replace(fromName, "_")
    End With
End Function

Upvotes: 1

Related Questions