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!
Bookmarks