0

I have a sheet which contains a column full of Item IDs called "Werkzeugtabelle Vormontage" and another sheet which contains part of the item IDs listed in sheet 1.

I want to filter Sheet 1 by the Item IDs that are similar to the ones in sheet 2. So basically have the sheet with more IDs chopped to the size of the sheet with less IDs. (Deleting the not similar ones would also be an option but no clue how that might work.)

If CheckSheet("BMV Vormontage") Then
    Sheets("Werkzeugtabelle").Select
    Sheets("Werkzeugtabelle").Copy After:=Sheets("BMV Vormontage")
    ActiveSheet.Name = "Werkzeugtabelle Vormontage"
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    Sheets("Restanschluss Vormontage").Select
    xRow = Cells(Rows.Count, 11).End(xlUp).Row
    'CountUnique ("K3:K100")
    'critCount = CountUnique.Count
    For i = 3 To lRow
        For a = 10 To xRow
            Sheets("Werkzeugtabelle Vormontage").Cells(i, 1).AutoFilter Field:=1, Criteria1:=Sheets("Restanschluss Vormontage").Cells(a, 11).Value
        Next a
    Next i
End If

The CheckSheet is looking for that sheet to get a starting point in the workbook. "Werkzeugtabelle" is the non filtered vanilla sheet.

Whenever I have more than one similar Item ID between the two sheets, it won't show, because I am only looking for one criteria it seems.

I tried to do a loop.

Community
  • 1
  • 1
user3079933
  • 63
  • 1
  • 9
  • Obligatory [How to avoid using Select](https://stackoverflow.com/q/10714251/8769365) link. – Nacorid Nov 29 '19 at 15:14
  • Are you sure, that you want to try to apply filter to row: `Sheets("Werkzeugtabelle Vormontage").Cells(i, 1).AutoFilter` ? And I don't think that you need to use loop for autofilter. Try to rephrase what do you need to have. – Vitaliy Prushak Nov 29 '19 at 15:29
  • I want to filter the "Werkzeugtabelle Vormontage" sheet so that it only shows the rows with the Item IDs that are identical to the IDs in the "Restanschuss Vormontage" sheet. Either by filtering the not needed IDs out or by deleting the whole rows and shifting the whole sheet up a row. – user3079933 Nov 29 '19 at 15:59

1 Answers1

0

Alright I guess I have found the solution. At least it does everything it does and doesn't spam me with error. Could you guys double check if this is a good code?

Sub Werkzeugtabelle_splitten()

Dim ws As Worksheet
Dim rng As Variant


Set ws = Sheets("Werkzeugtabelle")

' Splitten Vormontage
If CheckSheet("BMV Vormontage") Then
rng = Sheets("Restanschluss Vormontage").Range("K10:K100").Value

ws.Range("A3").AutoFilter _
Field:=1, _
Criteria1:=Application.Transpose(rng), _
Operator:=xlFilterValues
ws.Copy After:=Sheets("BMV Vormontage")
ActiveSheet.Name = "Werkzeugtabelle Vormontage"
ws.ShowAllData
End If

End Sub

So I have made that the orginial "Werkzeugtabelle" sheet will still exist and it only filters it > copies it to the right spot in the workbook and afterwards resets the filter on the original.

user3079933
  • 63
  • 1
  • 9