Sleeper: Combine data from several worksheets into one with conditions

[Chengo]

Hi all,

I'm looking for help with my data sorting.

I have 3 different Worksheets with the following data and I would like to combine them into the 4th Worksheet. But there are some requirements:

1. I’d like to have rows with same serial number under each other (row from Worksheet 1, then from Worksheet 2, and then from Worksheet 3) but so that the order from Worksheet 1 stays unchanged.
2. Then I’d like to remove any row that contains SP0 in it.

I have included a sample file for your reference.

Any help is most welcomed.

7738

[CatDaddy]

are you trying to remove rows with SP0 in the third column?

[CatDaddy]

Sub sort_delete()
Dim cell As Range, cell2 As Range
Dim r As Long, r2 As Long, a As Long
Dim f As Integer, sh As Integer
ActiveWorkbook.Sheets(1).Activate
'In Order Copy/Paste
Range("A1:C" & Range("A65536").End(xlUp).Row).Copy _
Destination:=Sheets(4).Range("A1")
For sh = 2 To 3
Sheets(sh).Activate
For Each cell In Range("A2:A" & Range("A65536").End(xlUp).Row)
r = cell.Row
f = 0
For a = Sheets(4).Range("A65536").End(xlUp).Row To 2 Step -1
If StrComp(cell.Text, Sheets(4).Range("A" & a).Text, vbTextCompare) = 0 Then
Sheets(sh).Select
Rows(r).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(4).Select
Rows(a + 1).Select
Selection.Insert shift:=xlDown
f = 1
Exit For
End If
Next a
If f = 0 Then
cell.EntireRow.Copy Destination:=Sheets(4).Range("A" & Range("A65536").End(xlUp).Offset(1, 0).Row)
End If
Next cell
Next sh
'Delete SP0
Sheets(4).Activate
For Each cell In Range("C2:C" & Range("A65536").End(xlUp).Row)
If InStr(cell.Text, "SP0") Then
cell.EntireRow.Delete shift:=xlUp
End If
Next cell
End Sub

Almost works, for some reason it is not deleting one line with SP0 in it...not sure why

[Chengo]

Hi CatDaddy,

This is exactly what I'm looking for, if only this one line gets sorted as well. What do you think keeps this one instance of SP0 still in the list?

[CatDaddy]

no clue, sorry mate

[Chengo]

Hi,

The code works on my sample data but I need some help to make it work in project spreadsheet.
Code currently calls out Sheets 1, 2 and 3 and put the results in Sheet 4.
I'd like it to call out data from renamed sheets - NIC1, NIC2 and ILO and put the results in sheet named ORDER.
I need to change it because I have more sheets in use and some of them are hidden. Also sheets NIC1, NIC2 and ILO are not first 3 sheets in the workbook.

Also I've decided to hide the rows with SP0 in them so I've changed the code to relflect the same.


'Hide SP0
Sheets(4).Activate
For Each cell In Range("C2:C" & Range("A65536").End(xlUp).Row)
If InStr(cell.Text, "SP0") Then
cell.EntireRow.Hidden = True
End If
Next cell
End Sub

Any suggestions?