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
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