Create Tabs based on a specific column in a sorted data worksheet

**rockell333** · 07-16-2012, 08:15 AM

I need to write a macro that will create a tab based on the change of data in a specific column.
My data spreadsheets all contain information about reps. For instance I run a monthly commissions report from our database into excel. It will list details on every job billed within the time frame i chose. Each record (row) contains both job info and the rep name attributed to that job. Once the data is sorted i need to create a tab based on the change in the rep column.

$549.00 $549.00 $0.00 Jeff Beck Client
$549.00 $549.00 $0.00 John Bonham Client
$947.38 $1,698.50 $0.00 Jimmy Page Client
$947.38 -$766.62 $0.00 John Paul Jones Client
$856.90 $856.90 $0.00 Perry Farrell Client

So let's just say the data above contained about 6000 lines each being a specific job and each job being tagged to a specific rep. Once i sort the data (or incorporate that into the macro) i want to have a macro that recognizes the change in rep in the rep column and creates a tab with the name of that column and places the data pertaining to that specific name in the worksheet..

**arlu1201** · 07-16-2012, 08:40 AM

Which is the column containing the rep name?

How many columns do you have in your main file?

**rockell333** · 07-16-2012, 09:50 AM

sorry for the delay.. i was on my way to work..

The rep column will be 16 and the total columns is 23.

**arlu1201** · 07-16-2012, 10:00 AM

Try this code

Option Explicit

Sub split_data()
Dim lrow As Long, i As Long
Dim sname As String

Application.ScreenUpdating = False

With Worksheets("Sheet1")
    .Sort.SortFields.Add Key:=Range("P:P") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
        .SetRange Range("A:W")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    For i = 2 To lrow
        If .Range("A" & i).Value <> "" Then
            sname = .Range("P" & i).Value
            If Not Evaluate("ISREF('" & sname & "'!A1)") Then
                Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sname
                .Rows("1:1").Copy Worksheets(sname).Range("A1")
            End If
            .Range("A" & i & ":W" & i).Copy Worksheets(sname).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
    Next i
End With

Application.ScreenUpdating = True

End Sub

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

**rockell333** · 07-16-2012, 10:13 AM

wait but it did create the tabs up to a certain point.. let me see what name it stopped at

**rockell333** · 07-16-2012, 10:21 AM

Thanks so much!! sorry last question.. is there a way to stop it too when it hits the blanks, there will be a section of blanks

**rockell333** · 07-16-2012, 10:12 AM

it got stuck here

If Not Evaluate("ISREF('" & sname & "'!A1)") Then

**rockell333** · 07-16-2012, 10:16 AM

ok i figured out why it hit a rep with a apostrophe that you cannot put as a worksheet name.. is there a workaround

**arlu1201** · 07-16-2012, 10:18 AM

Its nice to see you troubleshooting it yourself. Its one step forward in the learning process.

You can exclude the apostrophe or use a underscore if required.

**arlu1201** · 07-16-2012, 10:23 AM

The macro checks for the last row containing data and then creates the sheets.

Do you mean there will be blanks in between the data?

**rockell333** · 07-16-2012, 10:49 AM

No blanks in the middle because i presorted it .. there will be blanks at the end though which will give you an error message.. not a big deal because it is finished at that point because i dont need a tab for the blanks but could it just end once it finds blanks. and is there a way for it to ignore apostrophes in names like o'donnell so that it can create the tab.. if not i can fix that before hand

**arlu1201** · 07-16-2012, 11:02 AM

I edited the code to stop at blanks.

It will be good if you fix it earlier, because i am not aware of a fix for that. May take some time to do some research on how to fix it.

If i get it, i will let you know immediately.

**rockell333** · 07-16-2012, 11:13 AM

excellent thanks..

Create Tabs based on a specific column in a sorted data worksheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Create Tabs based on a specific column in a sorted data worksheet

Re: Create Tabs based on a specific column in a sorted data worksheet

Re: Create Tabs based on a specific column in a sorted data worksheet

Re: Create Tabs based on a specific column in a sorted data worksheet

Re: Create Tabs based on a specific column in a sorted data worksheet

Re: Create Tabs based on a specific column in a sorted data worksheet

Re: Create Tabs based on a specific column in a sorted data worksheet

Re: Create Tabs based on a specific column in a sorted data worksheet

Re: Create Tabs based on a specific column in a sorted data worksheet

Re: Create Tabs based on a specific column in a sorted data worksheet

Re: Create Tabs based on a specific column in a sorted data worksheet

Re: Create Tabs based on a specific column in a sorted data worksheet

Re: Create Tabs based on a specific column in a sorted data worksheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions