On Error Resume Next

jalrs

jalrs

Active Member
Joined
Apr 6, 2022
Messages
300
Office Version
  1. 365
Platform
  1. Windows
Hello guys,

Not sure if this is the right approach, but I think so.
I'm needing to add "On Error Resume Next" line to my code, so if it doesn't read the file with folha2.cells(i,5).value on the location, it resumes next i value. I just don't know where.
If this is not the right approach, feel free to suggest any other.

Here is my code:

VBA Code: 
Option Explicit
Sub integrarf1()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim livro1 As Workbook, livro2 As Workbook
Dim folha1 As Worksheet, folha2 As Worksheet, folha3 As Worksheet, folha4 As Worksheet
Dim ultimalinha1 As Long, ultimalinha2 As Long, ultimalinha3 As Long, ultimalinha4 As Long, ultimalinha5 As Long, ultimalinha6 As Long, ultimalinha7 As Long, i As Long
Dim localizacao As String, nomedocumento As String, valorfiltro As String

Set livro1 = ThisWorkbook
Set folha1 = livro1.Worksheets("PainelControlo")
Set folha2 = livro1.Worksheets("MACRO 1")
Set folha3 = livro1.Worksheets("Tipificação Feedback")

folha2.Activate

ultimalinha1 = folha2.Cells(Rows.Count, "E").End(xlUp).Row

    For i = 2 To ultimalinha1
    
        valorfiltro = Cells(i, 5).Value
    
        ultimalinha2 = folha3.Cells(Rows.Count, "B").End(xlUp).Row + 1
        ultimalinha3 = folha3.Cells(Rows.Count, "C").End(xlUp).Row + 1
        ultimalinha4 = folha3.Cells(Rows.Count, "D").End(xlUp).Row + 1
    
        Workbooks.Open Filename:=ThisWorkbook.Path & "\Controlo e Difusão\Feedbacks Recebidos\" & Cells(i, 5).Value & ".xlsx"
    
        Set livro2 = Workbooks("" & valorfiltro & ".xlsx")
        
        Set folha4 = livro2.Worksheets("Pendentes")
    
        ultimalinha5 = folha4.Cells(Rows.Count, "D").End(xlUp).Row + 1
        ultimalinha6 = folha4.Cells(Rows.Count, "AT").End(xlUp).Row + 1
        ultimalinha7 = folha4.Cells(Rows.Count, "AX").End(xlUp).Row + 1
        
            With folha4
            
                .Range("D2:D" & ultimalinha5).Copy
                folha3.Cells(ultimalinha2, 2).PasteSpecial Paste:=xlPasteValues
                
                .Range("AT2:AT" & ultimalinha6).Copy
                folha3.Cells(ultimalinha3, 3).PasteSpecial Paste:=xlPasteValues
                
                .Range("AX2:AZ" & ultimalinha7).Copy
                folha3.Cells(ultimalinha4, 4).PasteSpecial Paste:=xlPasteValues
                
            End With
            
        ActiveWorkbook.Close
        
        Application.CutCopyMode = False
        
        folha2.Activate
        
    Next i
    
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Any additional question feel free to ask
Any help is greatly appreciated.

Thanks!
 
Sort by date Sort by votes
Hey @GWteB ,

I hope you are doing fine.

Something came up, not an error with the macro tho.

Basically the code runs an error in case the folder is completely empty or all the files within the folder don't match the parametrized names on the excell cells. Thats a good error to show, and I addressed this to my work colleague, so he knows what to do if error 1004 is displayed. I told him to click "end" and check if the folder contains any file. In case of yes, check if those files match the supposed names, according to the cells (i,5).

Thing is, if I have 3 files with the name matched and 1 that doesn't match the name, it doesn't tell me any error, so without confirming, I don't know if it went 100% correct or only 75%. Is there anyway to prevent this?

Thanks, hope I was clear enough!
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Not sure if I understand correctly, because the scenario you describe in the first instance doesn't fit your original, now slightly modified code. After all, that code is based from the perspective of a known list of file names present on a worksheet, which list is compared name by name with a "collection" of names of files present on disk. In such a scenario, the On Error statement can be used to accomplish two goals at once: determine whether the file exists and if so, openening that file at the same time, and that's what the code does.

Instead, you describe a scenario from the point of view of existing files on disk, whose names are compared against a list of names on your worksheet. For such a scenario quite different code is needed and for now I can't come up with a functional use of the On Error statement in such a scenario.

jalrs said:
Thing is, if I have 3 files with the name matched and 1 that doesn't match the name, it doesn't tell me any error, so without confirming, I don't know if it went 100% correct or only 75%. Is there anyway to prevent this?
Click to expand...
In the context of your original scenario, I think I understand what you mean. I've amended the code in a way so it keeps track of whether a workbook was opened and processed or not. Changed code lines are preceded with ####, added lines are preceded with @@@@. The results will be displayed on a separate, newly created worksheet. See if this works for you.

VBA Code: 
Sub integrarf1_v5()
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Dim folha1 As Worksheet, folha2 As Worksheet, folha3 As Worksheet, folha4 As Worksheet
    Dim ultimalinha1 As Long, ultimalinha2 As Long, ultimalinha3 As Long, ultimalinha4 As Long, ultimalinha5 As Long, ultimalinha6 As Long, ultimalinha7 As Long, i As Long
    Dim localizacao As String, nomedocumento As String, valorfiltro As String
    
    With ThisWorkbook
        Set folha1 = .Worksheets("PainelControlo")
        Set folha2 = .Worksheets("MACRO 1")
        Set folha3 = .Worksheets("Tipificação Feedback")
    End With
        
    With folha2
        
        ultimalinha1 = .Cells(Rows.Count, "E").End(xlUp).Row
    
        ' Context: For i = 2 to ultimalinha1 :
        '    1st macro saves the template with Column E values as new name to folder "Anexos".
        '    2nd macro adds Column E doc names as attachments, from folder "Anexos".
        '    3rd macro (this particular one!!) should open Column E doc names values, from "Feedbacks Recebidos" folder.
        ' Next i        ^^^^^^^^^^^^^^^^^^^^^

        ' compose the desired location on disk to look in
        localizacao = ThisWorkbook.Path & "\Controlo e Difusão\Feedbacks Recebidos\"

        ' @@@@
        ' declare an undimensioned Array of type Variant and a counter of type Long (used as an increasing upper bound)
        Dim arr() As Variant, n As Long
        ' set upper bound of Array
        n = 1
        
        For i = 2 To ultimalinha1

            valorfiltro = .Cells(i, "E").Value
            
            ' @@@@
            Dim FileToOpen As String
            FileToOpen = localizacao & valorfiltro & ".xlsx"
            
            ' @@@@
            ' provide memory storage for each file name and a corresponding flag if opening succeeded
            ReDim Preserve arr(1 To 2, 1 To n)
            ' store file name
            arr(2, n) = FileToOpen
            n = n + 1

            ultimalinha2 = folha3.Cells(Rows.Count, "B").End(xlUp).Row + 1
            ultimalinha3 = folha3.Cells(Rows.Count, "C").End(xlUp).Row + 1
            ultimalinha4 = folha3.Cells(Rows.Count, "D").End(xlUp).Row + 1
        
            ' declare a variable of the appropriate type
            Dim JustOpenedWorkbook As Excel.Workbook

            ' try to open specific workbook in desired location on disk, catch its reference (if any) ...
            ' ... and store that reference in the variable "JustOpenedWorkbook".

            ' Prevent potential errors on invalid references within this loop
            Set JustOpenedWorkbook = Nothing
            
            ' ignore any error during this attempt
            On Error Resume Next
            
            ' ####
            Set JustOpenedWorkbook = Excel.Application.Workbooks.Open(Filename:=FileToOpen)

            ' restore error handler so future errors are not ignored anymore
            On Error GoTo 0

            ' check whether the variable "JustOpenedWorkbook" contains a valid reference (i.e. not being "Nothing")
            If Not JustOpenedWorkbook Is Nothing Then

                ' attempt to open workbook succeeded, proceed with desired actions
                
                ' @@@@
                ' store appropriate flag
                arr(1, n) = True
                
                Set folha4 = JustOpenedWorkbook.Worksheets("Pendentes")

                ultimalinha5 = folha4.Cells(Rows.Count, "D").End(xlUp).Row + 1
                ultimalinha6 = folha4.Cells(Rows.Count, "AT").End(xlUp).Row + 1
                ultimalinha7 = folha4.Cells(Rows.Count, "AX").End(xlUp).Row + 1

                    With folha4
                    
                        .Range("D2:D" & ultimalinha5).Copy
                        folha3.Cells(ultimalinha2, 2).PasteSpecial Paste:=xlPasteValues
                        
                        .Range("AT2:AT" & ultimalinha6).Copy
                        folha3.Cells(ultimalinha3, 3).PasteSpecial Paste:=xlPasteValues
                        
                        .Range("AX2:AZ" & ultimalinha7).Copy
                        folha3.Cells(ultimalinha4, 4).PasteSpecial Paste:=xlPasteValues
                        
                    End With
                Application.CutCopyMode = False
                
                ' close this one
                JustOpenedWorkbook.Close
            Else
                ' attempt to open specific workbook failed
                
                ' @@@@
                ' store appropriate flag
                arr(1, n) = False
            
            End If
        Next i
    End With
    
    ' @@@@
    ' show results on a separate, newly created worksheet
    With Excel.Application.Workbooks.Add.Worksheets(1)
        .Range("A1").Value = "File Name"
        .Range("B1").Value = "Succeeded?"
        .Range("A2").Resize(UBound(arr, 2), UBound(arr, 1)).Value = Excel.Application.Transpose(arr)
        .Range("A:B").Columns.AutoFit
    End With

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
GWteB said:
Not sure if I understand correctly, because the scenario you describe in the first instance doesn't fit your original, now slightly modified code. After all, that code is based from the perspective of a known list of file names present on a worksheet, which list is compared name by name with a "collection" of names of files present on disk. In such a scenario, the On Error statement can be used to accomplish two goals at once: determine whether the file exists and if so, openening that file at the same time, and that's what the code does.

Instead, you describe a scenario from the point of view of existing files on disk, whose names are compared against a list of names on your worksheet. For such a scenario quite different code is needed and for now I can't come up with a functional use of the On Error statement in such a scenario.


In the context of your original scenario, I think I understand what you mean. I've amended the code in a way so it keeps track of whether a workbook was opened and processed or not. Changed code lines are preceded with ####, added lines are preceded with @@@@. The results will be displayed on a separate, newly created worksheet. See if this works for you.

VBA Code: 
Sub integrarf1_v5()
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    Dim folha1 As Worksheet, folha2 As Worksheet, folha3 As Worksheet, folha4 As Worksheet
    Dim ultimalinha1 As Long, ultimalinha2 As Long, ultimalinha3 As Long, ultimalinha4 As Long, ultimalinha5 As Long, ultimalinha6 As Long, ultimalinha7 As Long, i As Long
    Dim localizacao As String, nomedocumento As String, valorfiltro As String
   
    With ThisWorkbook
        Set folha1 = .Worksheets("PainelControlo")
        Set folha2 = .Worksheets("MACRO 1")
        Set folha3 = .Worksheets("Tipificação Feedback")
    End With
       
    With folha2
       
        ultimalinha1 = .Cells(Rows.Count, "E").End(xlUp).Row
   
        ' Context: For i = 2 to ultimalinha1 :
        '    1st macro saves the template with Column E values as new name to folder "Anexos".
        '    2nd macro adds Column E doc names as attachments, from folder "Anexos".
        '    3rd macro (this particular one!!) should open Column E doc names values, from "Feedbacks Recebidos" folder.
        ' Next i        ^^^^^^^^^^^^^^^^^^^^^

        ' compose the desired location on disk to look in
        localizacao = ThisWorkbook.Path & "\Controlo e Difusão\Feedbacks Recebidos\"

        ' @@@@
        ' declare an undimensioned Array of type Variant and a counter of type Long (used as an increasing upper bound)
        Dim arr() As Variant, n As Long
        ' set upper bound of Array
        n = 1
       
        For i = 2 To ultimalinha1

            valorfiltro = .Cells(i, "E").Value
           
            ' @@@@
            Dim FileToOpen As String
            FileToOpen = localizacao & valorfiltro & ".xlsx"
           
            ' @@@@
            ' provide memory storage for each file name and a corresponding flag if opening succeeded
            ReDim Preserve arr(1 To 2, 1 To n)
            ' store file name
            arr(2, n) = FileToOpen
            n = n + 1

            ultimalinha2 = folha3.Cells(Rows.Count, "B").End(xlUp).Row + 1
            ultimalinha3 = folha3.Cells(Rows.Count, "C").End(xlUp).Row + 1
            ultimalinha4 = folha3.Cells(Rows.Count, "D").End(xlUp).Row + 1
       
            ' declare a variable of the appropriate type
            Dim JustOpenedWorkbook As Excel.Workbook

            ' try to open specific workbook in desired location on disk, catch its reference (if any) ...
            ' ... and store that reference in the variable "JustOpenedWorkbook".

            ' Prevent potential errors on invalid references within this loop
            Set JustOpenedWorkbook = Nothing
           
            ' ignore any error during this attempt
            On Error Resume Next
           
            ' ####
            Set JustOpenedWorkbook = Excel.Application.Workbooks.Open(Filename:=FileToOpen)

            ' restore error handler so future errors are not ignored anymore
            On Error GoTo 0

            ' check whether the variable "JustOpenedWorkbook" contains a valid reference (i.e. not being "Nothing")
            If Not JustOpenedWorkbook Is Nothing Then

                ' attempt to open workbook succeeded, proceed with desired actions
               
                ' @@@@
                ' store appropriate flag
                arr(1, n) = True
               
                Set folha4 = JustOpenedWorkbook.Worksheets("Pendentes")

                ultimalinha5 = folha4.Cells(Rows.Count, "D").End(xlUp).Row + 1
                ultimalinha6 = folha4.Cells(Rows.Count, "AT").End(xlUp).Row + 1
                ultimalinha7 = folha4.Cells(Rows.Count, "AX").End(xlUp).Row + 1

                    With folha4
                   
                        .Range("D2:D" & ultimalinha5).Copy
                        folha3.Cells(ultimalinha2, 2).PasteSpecial Paste:=xlPasteValues
                       
                        .Range("AT2:AT" & ultimalinha6).Copy
                        folha3.Cells(ultimalinha3, 3).PasteSpecial Paste:=xlPasteValues
                       
                        .Range("AX2:AZ" & ultimalinha7).Copy
                        folha3.Cells(ultimalinha4, 4).PasteSpecial Paste:=xlPasteValues
                       
                    End With
                Application.CutCopyMode = False
               
                ' close this one
                JustOpenedWorkbook.Close
            Else
                ' attempt to open specific workbook failed
               
                ' @@@@
                ' store appropriate flag
                arr(1, n) = False
           
            End If
        Next i
    End With
   
    ' @@@@
    ' show results on a separate, newly created worksheet
    With Excel.Application.Workbooks.Add.Worksheets(1)
        .Range("A1").Value = "File Name"
        .Range("B1").Value = "Succeeded?"
        .Range("A2").Resize(UBound(arr, 2), UBound(arr, 1)).Value = Excel.Application.Transpose(arr)
        .Range("A:B").Columns.AutoFit
    End With

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub
Click to expand...
Hey @GWteB ,

Glad I could explain myself. I Will test new version and deliver some feedback.

My apologies for not prevent this on the first go, my boss just performed more in depth tests and noticed that if a file existed but the name didnt match it could be left forgotten.

I thought on a sloppy approach while I waited for your feedback. I added a msg box to tell the end user to verify if all files were copied. Not much but at least addressing for a double check with the message box.

Thanks for your time Gwteb
 
Upvote 0
GWteB said:
Not sure if I understand correctly, because the scenario you describe in the first instance doesn't fit your original, now slightly modified code. After all, that code is based from the perspective of a known list of file names present on a worksheet, which list is compared name by name with a "collection" of names of files present on disk. In such a scenario, the On Error statement can be used to accomplish two goals at once: determine whether the file exists and if so, openening that file at the same time, and that's what the code does.

Instead, you describe a scenario from the point of view of existing files on disk, whose names are compared against a list of names on your worksheet. For such a scenario quite different code is needed and for now I can't come up with a functional use of the On Error statement in such a scenario.


In the context of your original scenario, I think I understand what you mean. I've amended the code in a way so it keeps track of whether a workbook was opened and processed or not. Changed code lines are preceded with ####, added lines are preceded with @@@@. The results will be displayed on a separate, newly created worksheet. See if this works for you.

VBA Code: 
Sub integrarf1_v5()
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    Dim folha1 As Worksheet, folha2 As Worksheet, folha3 As Worksheet, folha4 As Worksheet
    Dim ultimalinha1 As Long, ultimalinha2 As Long, ultimalinha3 As Long, ultimalinha4 As Long, ultimalinha5 As Long, ultimalinha6 As Long, ultimalinha7 As Long, i As Long
    Dim localizacao As String, nomedocumento As String, valorfiltro As String
   
    With ThisWorkbook
        Set folha1 = .Worksheets("PainelControlo")
        Set folha2 = .Worksheets("MACRO 1")
        Set folha3 = .Worksheets("Tipificação Feedback")
    End With
       
    With folha2
       
        ultimalinha1 = .Cells(Rows.Count, "E").End(xlUp).Row
   
        ' Context: For i = 2 to ultimalinha1 :
        '    1st macro saves the template with Column E values as new name to folder "Anexos".
        '    2nd macro adds Column E doc names as attachments, from folder "Anexos".
        '    3rd macro (this particular one!!) should open Column E doc names values, from "Feedbacks Recebidos" folder.
        ' Next i        ^^^^^^^^^^^^^^^^^^^^^

        ' compose the desired location on disk to look in
        localizacao = ThisWorkbook.Path & "\Controlo e Difusão\Feedbacks Recebidos\"

        ' @@@@
        ' declare an undimensioned Array of type Variant and a counter of type Long (used as an increasing upper bound)
        Dim arr() As Variant, n As Long
        ' set upper bound of Array
        n = 1
       
        For i = 2 To ultimalinha1

            valorfiltro = .Cells(i, "E").Value
           
            ' @@@@
            Dim FileToOpen As String
            FileToOpen = localizacao & valorfiltro & ".xlsx"
           
            ' @@@@
            ' provide memory storage for each file name and a corresponding flag if opening succeeded
            ReDim Preserve arr(1 To 2, 1 To n)
            ' store file name
            arr(2, n) = FileToOpen
            n = n + 1

            ultimalinha2 = folha3.Cells(Rows.Count, "B").End(xlUp).Row + 1
            ultimalinha3 = folha3.Cells(Rows.Count, "C").End(xlUp).Row + 1
            ultimalinha4 = folha3.Cells(Rows.Count, "D").End(xlUp).Row + 1
       
            ' declare a variable of the appropriate type
            Dim JustOpenedWorkbook As Excel.Workbook

            ' try to open specific workbook in desired location on disk, catch its reference (if any) ...
            ' ... and store that reference in the variable "JustOpenedWorkbook".

            ' Prevent potential errors on invalid references within this loop
            Set JustOpenedWorkbook = Nothing
           
            ' ignore any error during this attempt
            On Error Resume Next
           
            ' ####
            Set JustOpenedWorkbook = Excel.Application.Workbooks.Open(Filename:=FileToOpen)

            ' restore error handler so future errors are not ignored anymore
            On Error GoTo 0

            ' check whether the variable "JustOpenedWorkbook" contains a valid reference (i.e. not being "Nothing")
            If Not JustOpenedWorkbook Is Nothing Then

                ' attempt to open workbook succeeded, proceed with desired actions
               
                ' @@@@
                ' store appropriate flag
             arr(1, n) = True
               
                Set folha4 = JustOpenedWorkbook.Worksheets("Pendentes")

                ultimalinha5 = folha4.Cells(Rows.Count, "D").End(xlUp).Row + 1
                ultimalinha6 = folha4.Cells(Rows.Count, "AT").End(xlUp).Row + 1
                ultimalinha7 = folha4.Cells(Rows.Count, "AX").End(xlUp).Row + 1

                    With folha4
                   
                        .Range("D2:D" & ultimalinha5).Copy
                        folha3.Cells(ultimalinha2, 2).PasteSpecial Paste:=xlPasteValues
                       
                        .Range("AT2:AT" & ultimalinha6).Copy
                        folha3.Cells(ultimalinha3, 3).PasteSpecial Paste:=xlPasteValues
                       
                        .Range("AX2:AZ" & ultimalinha7).Copy
                        folha3.Cells(ultimalinha4, 4).PasteSpecial Paste:=xlPasteValues
                       
                    End With
                Application.CutCopyMode = False
               
                ' close this one
                JustOpenedWorkbook.Close
            Else
                ' attempt to open specific workbook failed
               
                ' @@@@
                ' store appropriate flag
                arr(1, n) = False
           
            End If
        Next i
    End With
   
    ' @@@@
    ' show results on a separate, newly created worksheet
    With Excel.Application.Workbooks.Add.Worksheets(1)
        .Range("A1").Value = "File Name"
        .Range("B1").Value = "Succeeded?"
        .Range("A2").Resize(UBound(arr, 2), UBound(arr, 1)).Value = Excel.Application.Transpose(arr)
        .Range("A:B").Columns.AutoFit
    End With

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub
Click to expand...
Hey @GWteB ,

It didn't work, "subscript out of range" on line
Rich (BB code): 
arr(1, n) = True

Perhaps what I'm asking is too much complex, and a messagebox, even if not a good practice, will eventually do the task for the moment.

Thank you!
 
Upvote 0
jalrs said:
It didn't work, "subscript out of range" on line
Code: 
arr(1, n) = True
Click to expand...
My bad, didn't actually test the code.
The n=n+1 line is on the wrong spot.

Try changing the code like this:
Rich (BB code): 
        ' set upper bound of Array - 1
        n = 0               ' <<<<<<<<<<<<<  (should have been 0  with the n=n+1  part on another place)
        
        For i = 2 To ultimalinha1

            ' increase upper bound
            n = n + 1             ' <<<<<<<<<<<<<  should have been at the start of the For-Next loop
            valorfiltro = .Cells(i, "E").Value
            
            ' @@@@
            Dim FileToOpen As String
            FileToOpen = localizacao & valorfiltro & ".xlsx"
            
            ' @@@@
            ' provide memory storage for each file name and a corresponding flag if opening succeeded
            ReDim Preserve arr(1 To 2, 1 To n)
            ' store file name
            arr(2, n) = FileToOpen

            ultimalinha2 = folha3.Cells(Rows.Count, "B").End(xlUp).Row + 1
 
Upvote 0
GWteB said:
My bad, didn't actually test the code.
The n=n+1 line is on the wrong spot.

Try changing the code like this:
Rich (BB code): 
        ' set upper bound of Array - 1
        n = 0               ' <<<<<<<<<<<<<  (should have been 0  with the n=n+1  part on another place)
       
        For i = 2 To ultimalinha1

            ' increase upper bound
            n = n + 1             ' <<<<<<<<<<<<<  should have been at the start of the For-Next loop
            valorfiltro = .Cells(i, "E").Value
           
            ' @@@@
            Dim FileToOpen As String
            FileToOpen = localizacao & valorfiltro & ".xlsx"
           
            ' @@@@
            ' provide memory storage for each file name and a corresponding flag if opening succeeded
            ReDim Preserve arr(1 To 2, 1 To n)
            ' store file name
            arr(2, n) = FileToOpen

            ultimalinha2 = folha3.Cells(Rows.Count, "B").End(xlUp).Row + 1
Click to expand...
Hey @GWteB ,

No problem about it, I will test later and deliver proper feedback.

Thanks for your effort !
 
Upvote 0
GWteB said:
My bad, didn't actually test the code.
The n=n+1 line is on the wrong spot.

Try changing the code like this:
Rich (BB code): 
        ' set upper bound of Array - 1
        n = 0               ' <<<<<<<<<<<<<  (should have been 0  with the n=n+1  part on another place)
       
        For i = 2 To ultimalinha1

            ' increase upper bound
            n = n + 1             ' <<<<<<<<<<<<<  should have been at the start of the For-Next loop
            valorfiltro = .Cells(i, "E").Value
           
            ' @@@@
            Dim FileToOpen As String
            FileToOpen = localizacao & valorfiltro & ".xlsx"
           
            ' @@@@
            ' provide memory storage for each file name and a corresponding flag if opening succeeded
            ReDim Preserve arr(1 To 2, 1 To n)
            ' store file name
            arr(2, n) = FileToOpen

            ultimalinha2 = folha3.Cells(Rows.Count, "B").End(xlUp).Row + 1
Click to expand...
Hey @GWteB ,

As promised, I tested and can confirm this works and does the intended!

Thank you very much for your help on this!
 
Upvote 0
You are welcome and thanks for letting me know.
 
Upvote 0
You must log in or register to reply here.

Similar threads

jalrs
  • Solved
My code only runs until the end if I double click my assigned macro button. Help Please
Replies
7
Views
395
rollis13
rollis13
jalrs
  • Solved
Remove line if x, add line if y
2
Replies
13
Views
696
jalrs
jalrs
M
  • Solved
VBA to Paste until last cell in column A
2
Replies
14
Views
147
MARK858
MARK858
H
  • Solved
Macro to speedy up process when copying Data
Replies
3
Views
126
Logit
Logit
C
  • Solved
Need Help VBA
2
Replies
10
Views
458
Trebor76
T

Forum statistics

Threads
1,225,203
Messages
6,183,555
Members
453,168
Latest member
Luggsy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top