PDA

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



RudaAgata
01-20-2014, 05:27 AM
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
01-21-2014, 02:02 AM
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
01-21-2014, 02:19 AM
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
01-21-2014, 02:23 AM
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
01-21-2014, 03:41 AM
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
01-21-2014, 05:02 AM
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
01-21-2014, 05:24 AM
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
01-21-2014, 09:31 AM
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