Sorting from a raw data base

[running54]

Hello -

I need help creating a vba code. I input data from a database to an excel sheet. I would like to sort this data into different spreadsheets. For instance: I want to sort "ele" and "ele-c" and have those criteria go to sheet one. Then have data "insta" go to sheet two, etc. The data I don't care about could remain on the input sheet. This is the code I have now. I want to be able to send two different criteria to one page.


Sub NotificationSort()
Dim SourceSheet As Worksheet
Dim TargetSheet As Worksheet
Dim SheetNames As Variant
Dim i As Long
Dim LR As Long
Set SourceSheet = Sheets("Data")
SheetNames = Array("ELE", "INSTA", "INSTF", "MW", "PFW")
Const FilterColumn = 6
With SourceSheet
LR = .Range("A" & .Rows.Count).End(xlUp).Row

For i = 0 To UBound(SheetNames)
Set TargetSheet = Worksheets(SheetNames(i))
TargetSheet.Cells.ClearContents

With .Range("A1:H" & LR)
.AutoFilter Field:=FilterColumn, Criteria1:=SheetNames(i)
.Offset(0, 0).Copy TargetSheet.Range("A1")
End With
Next i
End With
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

It depends a bit on just what might exist in column F.

The first option to consider might be to add the red text to your line as below

.AutoFilter Field:=FilterColumn, Criteria1:=SheetNames(i) & "*"

This would add any rows where column F begins with "ELE" to the ELE sheet
Note though that it would also transfer rows beginning with "INSTA" to the INSTA sheet etc

If that does not suit your data then changing that line to the following would just make the "begins with" apply to the ELE sheet (first sheet in your sheet array)

.AutoFilter Field:=FilterColumn, Criteria1:=SheetNames(i) & IIf(i = 0, "*", "")

But if column F might have values like "ELE-D" that you do not want moved to the ELE sheet then try

.AutoFilter Field:=FilterColumn, Criteria1:=IIf(i = 0, Array(SheetNames(i), SheetNames(i) & "-c"), SheetNames(i)), Operator:=xlFilterValues

 

[running54]

Thanks Peter! The first one worked great! That's all I needed!

 

[Peter_SSs]

OK, that's good.

It's not a big deal but Offset(0,0) of course doesn't offset at all, hence is not necessary.

<del>.Offset(0, 0)</del>.Copy TargetSheet.Range("A1")