Split into Separate Worksheets

Hi All,

Please see the example attached.

Column C contains the Agency name, I wish to separate all the one type of agencies into seperate worksheets and name the tab the agency name.

I have seen other code on this site pretty close to what I want but will not do exactly as wanted.

Can someone help out please!!

Thanks

Hi,

Add the following macro and run it. Please note one of your agencies contains a "/" character which is not allowed in a sheet name. The macro will replace a / with a "_"

Sub CreateAgencySheets()
    Dim ilastrow As Integer, iAgencies As Integer, x As Integer, rdata As Range
    Application.ScreenUpdating = False
    ilastrow = Range("A" & Rows.Count).End(xlUp).Row
    Set rdata = Sheet1.Range("A1").CurrentRegion
    Sheets.Add: ActiveSheet.Name = "temp"
    Range("A1") = "Agency": Range("C1") = "Agency"
    Sheet1.Range("C1:C" & ilastrow).AdvancedFilter Action:=xlFilterCopy, copytorange:=ActiveSheet.Range("A1"), unique:=True
    iAgencies = Range("A1").CurrentRegion.Rows.Count - 1
    
    For x = 1 To iAgencies
        Sheets("temp").Range("C2") = Sheets("temp").Range("A1").Cells(x + 1, 1)
        Sheets.Add after:=Sheets("temp")
        Sheet1.Range("1:1").Copy Range("A1")
        rdata.AdvancedFilter Action:=xlFilterCopy, copytorange:=ActiveSheet.Range("A1").CurrentRegion, criteriarange:=Sheets("temp").Range("C1:C2")
        If InStr(Sheets("temp").Range("A1").Cells(x + 1, 1), "/") > 0 Then Sheets("temp").Range("A1").Cells(x + 1, 1) = Replace(Sheets("temp").Range("A1").Cells(x + 1, 1), "/", "_")
        ActiveSheet.Name = Sheets("temp").Range("A1").Cells(x + 1, 1)

    Next x
    
    Application.DisplayAlerts = False
    Sheets("temp").Delete
End Sub

HTH

Richard,

Thanks a millon, this is exactly as want!!!.

I appreciate your time on this.

Thanks

Hi,

Thanks for the feedback. When you get a moment please visit your original post and mark the thread as closed - and rate the response if appropriate.

Regards

Richard,

Not sure what you mean by "Rate this thread", can you tell me more?

Richard you understood me issue and solved it in one answer, I appreciate it!!

James

Thanks James,

Sorry for the confusion, I should have said rate the answer not the thread. You can click on the little scales icon.

Regards

As the OP is using Excel 2007, I must point out that using Integer for declarations is fraught with danger.

Go ahead and declare them as Long so that you don't run into problems with those variables on large datasets.

Hi,

General point taken re Integer vs Long variable types.

In mitigation I calculated that there was likely to be less than 32000 unique agency types and that therefore this wouldn't be a problem.

Rgds