+ Reply to Thread
Results 1 to 1 of 1

Combine worksheets if...

Hybrid View

Airgaf Combine worksheets if... 05-05-2011, 01:05 PM
  1. #1
    Registered User
    Join Date
    02-23-2011
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    31

    Combine worksheets if...

    Hi everyone,

    I'm tinkering around with my macro right now and I need some help. At one point, my macro takes the data and splits it into several tabs according to name in a certain column.

    This part works great. The problem is that it splits it into too many tabs. I'd like to combine them (or just keep them from splitting) depending on the name of the labor type.

    What I'd like:

    1) Combine if the first 8 characters of the Labor type/worksheet name are identical.
    2) Also, combine all Clerical/Tech labor and Temp labor.
    3) If there are two sheets that fit criteria 1 or 2, take the second sheet and move the data (minus the header row) onto the first sheet in the next open spot.

    Here is my code that is used to split up the "Labor" tab into all the other tabs.

    Dim wsSheet, Lws As Worksheet
    Set Lws = Sheets("Labor")
    Dim lastRate, lastRate2, rTab, rtab2, nxtRrw, I As Integer
    Dim laborName, CostElm As String
    
    'Loop Through Cost Element List, Create Sheet and
    'Copy Row 1 (Labels) If Sheet Doesn't Exist
    
    'Current Last Row
        lastRate2 = Lws.Range("B" & Rows.Count).End(xlUp).Row
        
        For rTab = 2 To lastRate2
            Set wsSheet = Nothing
            laborName = Lws.Range("C" & rTab).Value
            On Error Resume Next
                Set wsSheet = Sheets(laborName & " Labor")
            On Error GoTo 0
                If wsSheet Is Nothing Then
                        Sheets.Add After:=Sheets(Sheets.Count)
                        ActiveSheet.Name = laborName & " Labor"
                        Lws.Rows("1:1").Copy _
                            Destination:=Sheets(laborName & " Labor").Range("A1")
                End If
        Next
        
    'Copy Rows To Next Open Row On Corresponding Labor Tab
        Lws.Select
            For rtab2 = 2 To lastRate2
                laborName = Sheets("Labor").Range("C" & rtab2).Value
                    nxtRrw = Sheets(laborName & " Labor").Range("C" & Rows.Count).End(xlUp).Row + 1
                    Range("A" & rtab2 & ":H" & rtab2).Copy _
                        Destination:=Sheets(laborName & " Labor").Range("A" & nxtRrw)
            Next
    I've also included a sample workbook which includes some of the different labor types and tabs that go into a normal workbook

    Thanks ahead of time!
    Attached Files Attached Files
    Last edited by Airgaf; 05-06-2011 at 01:41 PM. Reason: I figured it out

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1