Sheetname 'problem' and another Index-match problem

[Blackov]

first, I'm sorry for another index-match question, I have noticed a lot of these topic here. But I can't find one that helps me. Possible that I just suck a searching.
But I've learned a lot from this site.
Working on the Dutch version of 365

Question one:
My excel file consists of a, overview-page "TF overzicht" on which there is a table (TF_tabel) which the user fills in.
Example:
Name - Subject - Revision
TF01 - Subj1 - 00
TF02 - Subj2 - 00
TF03 - Subj3 - 00
TF03 - Subj3b - 01
TF04 - Subj4 - 00

After pressing a button, a worksheets gets copied for each name and renamed with "name_revision".
Afterwards, on the new sheet, the name and revision gets taken out of the sheetname and filled in 2 separate cells (AH1 and AH3).
Next, I need excel to find the "subject" in the table (shown above) based on name and revision, and put it in "I7" on the new sheet.
My first solution was a formula in each cell: "INDEX(TF_tabel[#Alles];VERGELIJKEN(1;(TF_tabel[[#Alles];[Kolom1]]=AH1)*(TF_tabel[[#Alles];[Kolom4]]=AH3);0);2)" (here is the dutch part)
(with AH1 the cell with the name, and AH3 with revision, note: AH3 is formatted as text, because revision numbering goes: 00, 01, 02, ... and excel doesn't like that, column 'revision in the table is also as text)
This works fine... Untill you've got 50 pages, with 6 of those links working together... Then excel gets slow...

So I tried putting it in VBA, so it would only run once per sheet when a button was pressed.
It does not work completely: The code finds the correct name on row x, and finds a match for 'revision 00' on the first line. I tried some variations on the code.
In the code below where is written "HERE" I tried putting +, & and ',' which all give different, but wrong, results.
Also, as long as the revision is '00', it will give , kind of, the correct Subject.
So after long search on google I found out that this code just searches for the first match of each, not the "and" match.

The main part of the code I use for this is:

Range("I7").Value = Application.Index(ws.ListObjects("TF_tabel").ListColumns(2).DataBodyRange, _
    Application.Match(Range("AH1"), ws.ListObjects("TF_tabel").ListColumns(1).DataBodyRange, 0) "HERE", _
    Application.Match(Range("AH3"), ws.ListObjects("TF_tabel").ListColumns(4).DataBodyRange, 0) - 1)

The "complete" code:

Sub FillData()
Dim fiche As String
Dim revisie As String
Dim temp As String
Dim ws As Worksheet
Set ws = Worksheets("TF overzicht")
Dim ws2 As Worksheet
Set ws2 = Worksheets("Werkblad")
On Error GoTo Fout

'I know I can put the name directly in the correct cell, but I was planning on using "fiche" and "revisie" somewhere else in the code, but that didn't happen
fiche = Left(ActiveSheet.Name, 4)
revisie = Mid(ActiveSheet.Name, InStr(ActiveSheet.Name, "_") + 1, 2)
Range("AH1").Value = fiche
Range("AH3").Value = revisie

'onderwerp (dutch for subject)
Range("I7").Value = Application.Index(ws.ListObjects("TF_tabel").ListColumns(2).DataBodyRange, _
    Application.Match(Range("AH1"), ws.ListObjects("TF_tabel").ListColumns(1).DataBodyRange, 0), _
    Application.Match(Range("AH3"), ws.ListObjects("TF_tabel").ListColumns(4).DataBodyRange, 0) - 1)

'this repeats multiple times for different cells/columns.
    
End If

Fout:
'some awesome error-handling code will be put here. With fireworks.
End Sub

Question two.
As I said in previous question, I copy and rename a sheet. In that code is an "IF loop" which checks if the name has been used or not, so no double sheets.
The loop just checks 'how many sheets' and then "for sheet1 - check name, for sheet2 - check name,...)
This works, however, with each sheet created the loop gets longer. So the loop gets VERY slow with 30+ sheets.
Is there a way to do this differently?
I was thinking creating a list with all the sheetnames to which you check your new name and add the new name afterwards.
Is that possible or is there a better/faster way?

Thanks in advance!

 

[Blackov]

So, I've been playing with stuff and I found something:

'This works:

Range("I7").Value = WorksheetFunction.Index(ws.ListObjects("TF_tabel").ListColumns(2).DataBodyRange, _
    Application.WorksheetFunction.Match(fiche, ws.ListObjects("TF_tabel").ListColumns(4).DataBodyRange, False))

'This also works, if I "cheat" and place the name and revision (eg: TF0301) in the first column:

Range("I7").Value = WorksheetFunction.Index(ws.ListObjects("TF_tabel").ListColumns(2).DataBodyRange, _
    Application.WorksheetFunction.Match(fiche & revisie, ws.ListObjects("TF_tabel").ListColumns(4).DataBodyRange , False))

However, this doesn't work. It gives me a type mismatch:
Range("I7").Value = WorksheetFunction.Index(ws.ListObjects("TF_tabel").ListColumns(2).DataBodyRange, _
    Application.WorksheetFunction.Match(fiche & revisie, ws.ListObjects("TF_tabel").ListColumns(4).DataBodyRange & ws.ListObjects("TF_tabel").ListColumns(4).DataBodyRange, False))

How can I fix this?

Another thing I have noticed, is something really weird...
This piece of code:

fiche = Left(ActiveSheet.Name, 4)
revisie = Mid(ActiveSheet.Name, InStr(ActiveSheet.Name, "_") + 1, 2)

for some reason calls a Private_sub on the sheet "TF overzicht".
This is the sub:

Private Sub Worksheet_Change(ByVal Target As Range) 
    If Target.Address = "$M$4" Then
        Application.ScreenUpdating = False
        Call Aanpassen
        Range("A10").Select ' Select cell A10.
        Application.ScreenUpdating = True
    End If
End Sub

It's a sub for checking if cell M4 on "TF overzicht" is updated.
So for some reason that sub is called and on the sheet "TF overzicht", AH1 is filled with "TF o" and AH3 is filled with "TF"
I don't understand why... I can understand where "TF" comes from, but the "o" is a mystery to me.
Can anyone explain?

Thanks!

 

[Blackov]

Found the solution. Thanks goes to this forum, for these many replies and possible solutions. Never have I seen such a devoted community!