Creating new tabs based on cell values

Hello people of the Excel Help Forum.

I'm a new member and this problem is one of the main factors for my membership.

I am trying to write a macro, but running into some difficulty. I have an excel workbook that contains one sheet, “Labor,” with the following information.
A B C D E F G H
1 Order No., Cost Elem, Cost Elem Name, ID Number, Cost Center, Amount, Quantity, and Posting Date
2 333458312 ... ... SAL-MGMT S/T ... ... ... ...
3 333458312 ... ... SAL-UNION S/T ... ... ... ...
4 333458312 ... ... EMP TRVL-MEALS/ENT... ... ... ...

and so on. I am going to run this macro on multiple spreadsheets (but only one at a time). They all have 8 columns, but the numbers of rows vary (aka dynamic range). The column I care about is C (Cost Element Name). I want to make a new tab for all of the different labor types.
There are several cost element names, but I only care about 5 of them. They are: “SAL-MGMT S/T”, “SAL-CLERICAL/TECH ST”, “SAL-UNION S/T”, “SAL-TEMP P-T S/T”, and “SRV-TEMP AGNCY LABOR.” The five cost element names fall into 4 different categories: MGMT Labor, CT Labor (contains CLERICAL/TECH labor and TEMP P-T), Union Labor, and Agency Labor. I want the new spreadsheets in the same workbook and named after the four categories. So all the Union labor would be in the “Union Labor” sheet along with all the information in their row. Also, if the Cost Element Name does not fall under one of the five names I want, I want that row deleted (My example row #4 would be deleted since the cost element name is EMP TRVL-MEALS/ENT.)

I’ve tried adapting the code from the answer to this question (http://www.computing.net/answers/off...ets/13596.html), but I keep getting this error, “Runtime '1004' application-defined or object-defined error” for the line that reads ActiveSheet.Name = rateName.

Here is my code:

Sub AssignTabsNew()

Dim wsSheet As Worksheet
Dim lastLabor, LTab, nxtRrw As Integer
Dim laborName As String

'Determine Last Row in Labor Type List
lastLabor = Sheets("Labor").Range("C" & Rows.Count).End(xlUp).Row
'Loop Through Labor Type List, Create Sheet and
'Copy Row 1 (Labels) If Sheet Doesn't Exist
    For LTab = 2 To lastLabor
        Set wsSheet = Nothing
        laborName = Sheets("Labor").Range("C" & LTab).Text
        On Error Resume Next
            Set wsSheet = Sheets(laborName)
        On Error GoTo 0
            If wsSheet Is Nothing Then
                    Sheets.Add after:=Sheets(Sheets.Count)
                    ActiveSheet.Name = laborName
                    Sheets("Labor").Rows("1:1").Copy _
                        Destination:=Sheets(laborName).Range("A1")
            End If
    Next
    
'Copy Rows To Next Open Row On Corresponding Labor Tab
    For LTab = 2 To lastLabor
        laborName = Sheets("Labor").Range("C" & LTab).Value
            nxtRrw = Sheets(laborName).Range("C" & Rows.Count).End(xlUp).Row + 1
            Range("A" & LTab & ":H" & LTab).Copy _
                Destination:=Sheets(laborName).Range("A" & nxtRrw)
    Next
    
End Sub

Hi,

Welcome to the forum.
Can you post a copy of your workbook.

HI,

Too let you know that you can not as far as I know create a New worksheet that contains
"Spaces, "/"". is there another way you want to name the sheets.
It seem you can not use "/" as part of the sheet name.

Airgaf,

I'm coding to change the "/" to "-"
If that's ok.

med1234,

Welcome to the forum. Please start your own thread for this request.