Loop Working but gives me a run Time Error 1004

jalrs

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

As title says, I'm getting a run time error 1004 even tho my loop is working as intended. I can't find any solution to stop this, so hopefully here. Error as attachment, vba code follows:

VBA Code:
Sub myloopattempt()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim lr1 As Long, lr2 As Long, lr3 As Long, lr4 As Long, i As Long
Dim mypath As String, docname As String, valorfiltro As String

Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("Stock")
Set ws2 = wb1.Worksheets("MACRO 1")

lr1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
lr2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row

ws2.Activate

    For i = 2 To lr1
        
        valorfiltro = Cells(i, 1).Value
        
        Workbooks.Open Filename:=ThisWorkbook.Path & "\Temp\ST_TEMPLATE_" & Cells(i, 1).Value & ".xlsx"
        
        Set wb2 = Workbooks("ST_TEMPLATE_" & valorfiltro & ".xlsx")
        
        Set ws3 = wb2.Worksheets("Pendentes")
        
        ws3.Activate
        
        ws3.UsedRange.Offset(1).ClearContents
        
        lr3 = ws3.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        ws1.Activate
        
        With ws1.Range("A5:AV" & lr1)
        
            .AutoFilter 46, valorfiltro
            .AutoFilter 47, "Em tratamento"
            
            With ws1
        
            .Range("A6:AV" & lr1).Copy ws3.Cells(2, 1)
            .Range("BH6:BH" & lr1).Copy ws3.Cells(2, 49)
            
            End With
            
            .AutoFilter
        
        End With
        
        lr3 = ws3.Cells.Find("*", , xlFormulas, , 1, 2).Row + 1
        ws3.Range("A" & lr3 & ":A1001").EntireRow.Delete
        
        wb2.Activate
        
        ws3.Activate
        
        lr4 = Cells(Rows.Count, "AT").End(xlUp).Row
        
        If lr4 > 1 Then
        
            Range("AY2:AY" & lr4).FormulaR1C1 = _
            "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],TAB_FDB!C[-50]:C[-49],2,0))"
            
        End If
        
        ws3.Protect Password:="blabla", _
        DrawingObjects:=True, _
        Contents:=True, _
        Scenarios:=True, _
        UserInterfaceOnly:=True, _
        AllowFormattingCells:=False, _
        AllowFormattingColumns:=False, _
        AllowFormattingRows:=False, _
        AllowInsertingColumns:=False, _
        AllowInsertingRows:=False, _
        AllowInsertingHyperlinks:=False, _
        AllowDeletingColumns:=False, _
        AllowDeletingRows:=False, _
        AllowSorting:=True, _
        AllowFiltering:=False, _
        AllowUsingPivotTables:=False

        mypath = ThisWorkbook.Path & "\Anexos\"
            
        wb1.Activate
        
        ws2.Activate
        
        docname = Cells(i, 5).Value
        
        wb2.Activate
        
        ws3.Activate
        
        ActiveWorkbook.SaveAs Filename:=mypath & docname & ".xlsx", FileFormat:=xlOpenXMLWorkbook
    
        ActiveWorkbook.Close
        
    Next i
    
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Any help is appreciated
 

Attachments

  • runtime.png
    runtime.png
    11 KB · Views: 10

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Isn't there a character that cannot be used in the file name of column A?
 
Upvote 0
Isn't there a character that cannot be used in the file name of column A?
Hello Takae, thanks for the reply.

I actually have no clue, column A follows as attachment.

Thanks!
 

Attachments

  • columnA.png
    columnA.png
    2.3 KB · Views: 9
Upvote 0
Hello Takae, thanks for the reply.

I actually have no clue, column A follows as attachment.

Thanks!
On which line does the error occur? Also you are handling some worksheets, I recommended to specify the sheet name. Like "valorfiltro = ws2.Cells(i, 1).Value".
 
Upvote 0
On which line does the error occur?
On this line:
VBA Code:
Workbooks.Open Filename:=ThisWorkbook.Path & "\Temp\ST_TEMPLATE_" & Cells(i, 1).Value & ".xlsx"

Note that I get the desired output, last filled row is row 6, having into account I have headers on row 1, I get the 5 files as intended. Shown as attachment
 

Attachments

  • Output.png
    Output.png
    18.4 KB · Views: 6
Upvote 0
The first snapshot doesn't say "Cells(i, 1).Value". Please check Col A of Worksheets("MACRO 1").
 
Upvote 0
The first snapshot doesn't say "Cells(i, 1).Value". Please check Col A of Worksheets("MACRO 1").
Sorry, could you elaborate? I don't get what you mean, I'm sorry
 
Upvote 0
Sorry, could you elaborate? I don't get what you mean, I'm sorry
The error msg said "can not access ----\Temp\ST_TEMPLATE_.xlsx" but your code said "\Temp\ST_TEMPLATE_" & Cells(i, 1).Value & ".xlsx" it means cells(i,1).value is empty.
So please check col A of ws2(=Worksheets("MACRO 1"):Because you specified ws2 just before.)
 
Upvote 0
The error msg said "can not access ----\Temp\ST_TEMPLATE_.xlsx" but your code said "\Temp\ST_TEMPLATE_" & Cells(i, 1).Value & ".xlsx" it means cells(i,1).value is empty.
So please check col A of ws2(=Worksheets("MACRO 1"):Because you specified ws2 just before.)
It is empty as intended. I only have 5 names. I can have more in the future and the proccess is the same, that's why i chose a loop for my actions. I thought the loop would stop when the cell is empty. How do I set this? Thanks
 
Upvote 0
The loop works until lr1. I think it should be lr2...?
And "Activate" slows down the work. You don't need to like "Activate ws2" if you write "ws2.cells(i,2).value".
 
Last edited:
Upvote 0
Solution

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

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