Macro that creates new sheet and copies select rows

[awibbing96]

Hello,

I am writing a macro that splits out data to another sheet after
comparing values in column A to a set variable.

Column A has all Work Order numbers. I need the macro to create
a new sheet for each new work order, and copy all lines to that sheet.
It needs to name the sheet with a concatenation of the value
found in column A and Column B, and then copy all rows associated with
that Work Order to the new sheet.

Here is an example of what I am trying to manipulate:
A B C D E F
prefix suffix product labor_minutes operation units
12581 4 SSWR2755F.B-100 283.2 30 50
12581 4 SSWR2755F.B-100 90 70 50
12581 4 SSWR2755F.B-100 82.2 80 50
12695 4 SSWR2755F.B-100 173.4 250 50
12695 4 SSWR2755F.B-100 24.6 250 50
12695 4 SSWR2755F.B-100 16.8 250 50
12695 4 SSWR2755F.B-100 254.4 250 50
12695 4 SSWR2755F.B-100 55.8 250 50

The prefix is the Work Order. Prefix 12581 should get its own sheet, named
12581-4, and prefix 12695 should get its own sheet named 12695-4.
All rows for 12581 should be copied into the sheet named 12581.

This needs to be a loop since there are 39000 rows to sift through.

I thought I'd need to declare a variable for the Work Order/prefix
and have the macro compare it to the values in column A. If the value in column
A does not match the variable it would create a new sheet and copy over all columns
associated with that Work Order.

My code so far:


Sub SortDataByWOxyz()

Option Explicit

Dim Variable_One as Integer
Dim Variable_Two As Integer
Dim cell As Range
Dim Sht_name As String
Dim intLastRow As Integer
Dim intSuffix As Integer

Set Variable_One = 0
Set intSuffix = .Range("b2", .Range("b2").End(xlDown))

intLastRow = ActiveSheet.UsedRange.RowsCount
Set cell = Range ("a2:a" & intLastRow) 'this is so the macro
doesn't keep trying to process rows without values



For Each cell In Sheets(1).Range("A2:F39986") 'Ok, another issue
is that I can't be certain the range of every report I am applying
this macro to. Is there some way to make the range globally applicable, like:
Dim SourceRange As Range
Set SourceRange= .Range("a2", .Range("A2").End(xlDown).End(xlToRight))


If cell.Value <> Variable_One Then

'Grab prefix - suffix
'Create sheet with sheet name
'Copy data

Sheets.Add.Name = Range(a2:a" & intSuffix).value
cell.EntireRow.CopyDestination:=Sheets(Sht_name).Range("a" & cell.Row)

Else: cell.EntireRow.Copy Destination:=Sheets(Sht_name).Range("a" & cell.Row)
End If
Next

End Sub

 

[awibbing96]

I see my example was messed up after I posted it so here is a screencap: