macro code working for 1 file but not when trying to apply for the range of files

[RudaAgata]

Hallo,
thank you for your help on the previous thread! I have now another issue. I have created a macro code that does following: opens a pivot, finds a specific file (in the pivot filter), opens that specific file from another location, in that file - it creates a new tab with latest pivot data for that file. In general the whole code works fine but only if I work with 1 file. When I tried to apply it for the range of files (column in Excel with files name listed one under another), then macro works fine only in the first top one and in another ones it creates a new tab but places it randomly. As in the file I have tabs with different names the macro needs to place it in a good location - as the last one with that spefic tab name (and therefore I cannot use the worksheet.Count)..And in case I select and process file by file it works fine but when I tell macro to do the range of files - it works fine only in the first one, the next he is placing it randomly...) please help! Best regards Agata
here is the text (i pasted the releavnt part)

Private Sub CommandButton2_Click()
Dim NaamString As String
    Dim verkortString As String
    Dim DoeString As String
    Dim SaveString As String
SaveString = Sheets("wORKFLOW TOOL").Range("h1")
    Windows("Refresh tool.xlsm").Activate
    verkortString = ""
    verkortString = Sheets("Workflow tool").Range("K" + CStr(ic))
    NaamString = verkortString + ".xlsm"
    DoeString = Sheets("Workflow tool").Range("J" + CStr(ic)).Value
    If aic <> 1 Then If Sheets("Workflow tool").Range("J" + CStr(ic)).Interior.ColorIndex = 3 Then GoTo kleur
   sn = 0
    su = Range("C7")
    Dim CoString As String
    CO = Range("J2")
'SEAF
    If CO = "SEAF" And cp = 0 Then Workbooks.Open Filename:= "...../pivots.xlsx"          
    If CO = "SEAF" Then Windows("pivotS.xlsx").Activate
    If CO = "SEAF" Then Sheets("BDL").Select     'later aanpassen
    If CO = "SEAF" Then cp = 1
    If CO = "SEAF" Then Sheets(1).Range("B2") = DoeString
    If CO = "SEAF" Then GoTo lalala
lalala:
clo = 1
regels = su
a1 = aantalregels
af = 0
d1 = su
For rr = regels To 4 Step -1
If Sheets(1).Range("A" + CStr(rr)).Value = "" And af = 0 Then d1 = d1 - 1 Else af = 1
Next rr
   st = 10
   Sheets(1).Range("A" + CStr(5) & ":n" + CStr(d1)).Copy
    If CO = "SEAF" Then Workbooks.Open Filename:= _
        "..../SEAF/" + NaamString
Windows(NaamString).Activate
Dim WS As Worksheet, iCnt As Long
Dim WW As Worksheet
For Each WS In ActiveWorkbook.Worksheets
Debug.Print WS.Name
If InStr(1, Left(WS.Name, 3), "bdl", vbTextCompare) > 0 Then ' will check for start with "bdl"..
                myTotal = myTotal + 1
'If InStr(1, WS.Name, "bdl", vbTextCompare) = 1 Then iCnt = iCnt + 1
'Set WW = WS
End If
Next WS
b1 = myTotal
b2 = b1
 b3 = 0
    Sheets.Add after:=Sheets(b2)
    Sheets(b2 + 1).Name = SaveString

[westconn1]

as you have only pasted the relevant (to you) code
i can not see if anywhere, you reset mytotal = 0 for each opened workbook, but assume not

[RudaAgata]

here is more of the code after

Sheets.Add after:=Sheets(b2)
    Sheets(b2 + 1).Name = SaveString  
 Sheets(SaveString).Select
    Sheets(SaveString).Range("A2").Select
    ActiveSheet.Paste
    ActiveWindow.Zoom = 75
    Sheets(b2 + 1).Columns("A:n").Select
    Sheets(b2 + 1).Columns("A:n").EntireColumn.AutoFit
    Sheets(b2 + 1).Select
    e1 = 1
    e2 = 1
    Sheets(b2).Range("A" + CStr(e1) & ":o" + CStr(e2)).Copy
    Sheets(b2 + 1).Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
totaal = myTotal
aantal = totaal
s = 0
For R = totaal To 1 Step -1
If Sheets(R).Range("A1").Value = "" And s = 0 Then aantal = aantal - 1
If Sheets(R).Range("A1").Value <> "" Then s = 1
Next R

[RudaAgata]

after that the macro does some calculations within the data under newly added tab but because it needs to read data from the previous "bdl" tab the new "bdl" tab needs to be exactly after the previous "bdl" tab, I just do not get why it creates it randomly when it runs file by file in one go, but when i do manualy 1 by 1 it works fine....

[westconn1]

when opening each file, unless you reset mytotal = 0 it will be cumulative for all the opened files, probably greater than the number of worksheets, in some of the workbooks, therefore invalid or if valid, appearing random

but because it needs to read data from the previous "bdl" tab the new "bdl" tab needs to be exactly after the previous "bdl" tab,

it would be better to set the appropriate sheets to sheet object variables, to work with

[RudaAgata]

when opening each file, unless you reset mytotal = 0 it will be cumulative for all the opened files, probably greater than the number of worksheets, in some of the workbooks, therefore invalid or if valid, appearing random

thank you for your reply - the macro is opening 1 file then creates the new tab plus some other calculations and then closing that file, then it opens the next one - creates the new tab etc etc... so there is only one specific file open at the time macro is busy, so it cannot count all "bdl" from other files...

[RudaAgata]

or even if the files are curerctly closed it still counts all "bdl" tabs also from the opened and then closed ones? (like store them in memory or so?), if so - how can i reset mytotal so it only counts the ones in active Naamstring file ?i doulbed checked it again, only one NammString file is open when macro calculates....

[SamT]

That is not the entire code, so I can't help you.

I cannot tell where certain values are being set.
I cannot tell where certain procedures start or end.
I cannot tell where certain procedures are called.
I cannot see where you loop thru the workbooks.
I cannot tell where the label "kleur:" is located.

Especially, I cannot see where "myTotal" is reset to 0 for each workbook.

You can try this change to see if it works.

Dim WS As Worksheet, iCnt As Long
    Dim WW As Worksheet
mytotal = 0 '<--- Change here
    For Each WS In ActiveWorkbook.Worksheets
        Debug.Print WS.Name
        If InStr(1, Left(WS.Name, 3), "bdl", vbTextCompare) > 0 Then ' will check for start with "bdl"..
            myTotal = myTotal + 1