On Error Resume Next

[jalrs]

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:

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!

 

[GWteB]

Probably this will resolve your issue ...

Sub integrarf1_v2()

    ' 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.
    
    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
        
            Dim JustOpenedWorkbook As Excel.Workbook
            On Error Resume Next
            Set JustOpenedWorkbook = Excel.Application.Workbooks.Open(Filename:=ThisWorkbook.Path & "\Controlo e Difusão\Feedbacks Recebidos\" & Cells(i, 5).Value & ".xlsx")
            On Error GoTo 0
            
            If Not JustOpenedWorkbook Is Nothing Then
                
                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
            
            End If
        Next i
        
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

 

[jalrs]

Probably this will resolve your issue ...

Sub integrarf1_v2()

    ' 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.
   
    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
       
            Dim JustOpenedWorkbook As Excel.Workbook
            On Error Resume Next
            Set JustOpenedWorkbook = Excel.Application.Workbooks.Open(Filename:=ThisWorkbook.Path & "\Controlo e Difusão\Feedbacks Recebidos\" & Cells(i, 5).Value & ".xlsx")
            On Error GoTo 0
           
            If Not JustOpenedWorkbook Is Nothing Then
               
                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
           
            End If
        Next i
       
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

Hey GWteB,

I'm having a subscript out of range error on line

Set livro2 = Workbooks("" & valorfiltro & ".xlsx")

Thanks for your time!

 

[Alex Blakenburg]

That typically means the workbook name is not correct.
Is the workbook an xlsx file extension ?
Is the workbook open ?

If yes, then try adding this line before the Set livro2 line.

Debug.Print "" & valorfiltro & ".xlsx"

Then check what is in you Immediate window and make sure it is the correct name for the open workbook (to the letter including no additional spaces in either)

 

[RoryA]

You don't need livro2. JustOpenedWorkbook is already a reference to that workbook.

 

[GWteB]

Hey GWteB,

I'm having a subscript out of range error on line

Set livro2 = Workbooks("" & valorfiltro & ".xlsx")

Thanks for your time!

Glad to help. I would like to note that we only see your code. It's hard to guess what you're ultimately aiming for (and in this case with which workbook, so @RoryA's assumption might just turn out to be wrong...). The line causing the error was already present in your original code, @Alex Blakenburg has already responded with the most likely cause.

You are the only one who knows what you want. I realize that it's sometimes difficult to convey those wishes in a clear way, but helpers on this forum can not do without.

 

[RoryA]

Set JustOpenedWorkbook = Excel.Application.Workbooks.Open(Filename:=ThisWorkbook.Path & "\Controlo e Difusão\Feedbacks Recebidos\" & Cells(i, 5).Value & ".xlsx")

and

valorfiltro = Cells(i, 5).Value
...
Set livro2 = Workbooks("" & valorfiltro & ".xlsx")

I'm pretty sure I'm right.

 

[GWteB]

Ah, I see ... Didn't delve that deep into the code provided by the OP . He/she should now be on the right track.

 

[jalrs]

Hello guys,

Let me start with thanking each of you for your time here, @Alex Blakenburg , @GWteB and @RoryA .

Glad to help. I would like to note that we only see your code. It's hard to guess what you're ultimately aiming for (and in this case with which workbook, so @RoryA's assumption might just turn out to be wrong...). The line causing the error was already present in your original code, @Alex Blakenburg has already responded with the most likely cause.

You are the only one who knows what you want. I realize that it's sometimes difficult to convey those wishes in a clear way, but helpers on this forum can not do without.

You are absolutely correct and my apologies for not being clear. Can provide pictures if needed. Doubt sharing the workbook would make any difference, but you tell me.

Basically, I have one MasterWorkbook where I run my macros. When I run my first macro, what it does is it filters accordingly to each responsible department data, copies that data, and pastes into each responsible department template (new workbook, one for each department) with a new name - cells(i,5).value - and new location, so we preserve the templates.

My second macro generates the e-mail but first it checks on the attachments location to see if A2 is blank. If A2 is blank there is no data to be shared, therefore we don't send the email to that area.

Here, on my 3rd macro, I have to integrate the answers received from the email, on my MasterWorkBook. I manually save them on Feedbacks Recebidos folder.
The sent file name is the same as the received file name, that's why I tell to open cells(i,5).Value.

But accordingly to second macro, if I don't have nothing to send, I won't receive anything. So it should skip the i value that's non existant.

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 should open Column E doc names values, from "Feedbacks Recebidos" folder. Next i

Please look at attachments.

YourCodeWorking.Png I assume every department has data, therefore I received answers from all of them.
YourCodeNotWorking.Png I assume PósVenda department had no data, therefore I didn't email them, therefore I didn't get any answer, therefore file doesn't exist. So it should skip and move to Transportes. Thats what I'm addressing with this thread.
ImmediateWindow.Png green rectangle was on my first run, where it worked with first assumption and red rectangle was on my second run, where it didn't work with second assumption

I hope it is now clear and sorry if I extended myself too much. Any question feel free to ask.

Thank you!

That typically means the workbook name is not correct.
Is the workbook an xlsx file extension ?
Is the workbook open ?

If yes, then try adding this line before the Set livro2 line.

Debug.Print "" & valorfiltro & ".xlsx"

Then check what is in you Immediate window and make sure it is the correct name for the open workbook (to the letter including no additional spaces in either)

I tried that and it looked good, maybe I don't know how to interpret it. "Please see attachment called Immediate Window"
Thank you.

Set JustOpenedWorkbook = Excel.Application.Workbooks.Open(Filename:=ThisWorkbook.Path & "\Controlo e Difusão\Feedbacks Recebidos\" & Cells(i, 5).Value & ".xlsx")

and

valorfiltro = Cells(i, 5).Value
...
Set livro2 = Workbooks("" & valorfiltro & ".xlsx")

I'm pretty sure I'm right.

I didn't understand what you meant but I bet, between the 4 of us, I'm the only one wrong here.
If you could explain again, I'd be very grateful
Thank you.

 

[GWteB]

Thank you for your detailed explanation. I think it's clear to me.
I've slightly modified my post #2 code by applying @RoryA's suggestion: deleting a redundant line.
I've also added some comments to the code to clarify things.
See if this works for you.

Sub integrarf1_v3()

    ' 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.
    
    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\"

        For i = 2 To ultimalinha1

            valorfiltro = .Cells(i, "E").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
        
            ' 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".

            ' ignore any error during this attempt
            On Error Resume Next
            Set JustOpenedWorkbook = Excel.Application.Workbooks.Open(FileName:=localizacao & valorfiltro & ".xlsx")

            ' 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
                
                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, do nothing
            End If
        Next i
        
    End With
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub