How can I make my loop move on to the next workbook without repeating itself in the first workbook?

user_16091232 0
Viewed 79

I just want to insert 3 rows with formulas but these rows keep inserting over and over again in the first opened workbook.

For the sake of discussion, would it be better to use a For Each loop instead?

Sub FXBuy()

Dim wb As Workbook

Dim PathName$, FileName$

    PathName = "H:\BASEL Reporting - Oliver's Mock\FX\"
    FileName = Dir(PathName)
    Do While FileName <> ""
    Set wb = Workbooks.Open(PathName & FileName)

wb.Sheets("FX").Columns("W:X").Hidden = True
wb.Sheets("FX").Columns("AD:AI").Hidden = True

wb.Sheets("FX").Columns("U:AB").SpecialCells(xlCellTypeVisible).Interior.Color = 65535
 

LastRow = wb.Sheets("FX").Columns("B").Find("B", LookAt:=xlPart).MergeArea.Row + wb.Sheets("FX").Columns("B").Find("B", LookAt:=xlPart).MergeArea.Rows.Count - 1

wb.Sheets("FX").Range("A" & LastRow & ":A" & LastRow + 2).EntireRow.Insert

wb.Sheets("FX").Range("S" & LastRow) = 20
wb.Sheets("FX").Range("S" & LastRow + 1) = 50

wb.Sheets("FX").Range("U" & LastRow).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.2,U$3:U$" & LastRow - 1 & ")"
wb.Sheets("FX").Range("Z" & LastRow).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.2,Z$3:Z$" & LastRow - 1 & ")"
wb.Sheets("FX").Range("AB" & LastRow).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.2,AB$3:AB$" & LastRow - 1 & ")"

wb.Sheets("FX").Range("U" & LastRow + 1).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.5,U$3:U$" & LastRow - 1 & ")"
wb.Sheets("FX").Range("Z" & LastRow + 1).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.5,Z$3:Z$" & LastRow - 1 & ")"
wb.Sheets("FX").Range("AB" & LastRow + 1).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.5,AB$3:AB$" & LastRow - 1 & ")"

wb.Sheets("FX").Range("U" & LastRow + 2).Formula = "=SUM(U" & LastRow & ",U" & LastRow + 1 & ")"
wb.Sheets("FX").Range("Z" & LastRow + 2).Formula = "=SUM(Z" & LastRow & ",Z" & LastRow + 1 & ")"
wb.Sheets("FX").Range("AB" & LastRow + 2).Formula = "=SUM(AB" & LastRow & ",AB" & LastRow + 1 & ")"

Loop

End Sub
while-loop vba excel
2 Answers

Your code would highly benefit from a With wb.Sheets("FX") statement so you don't need to repeat that 100 times.

Your issue is that you need a FileName = Dir() right before Loop to move to the next file.

Public Sub FXBuy()
    Dim PathName As String
    PathName = "H:\BASEL Reporting - Oliver's Mock\FX\"
    
    Dim FileName As String
    FileName = Dir(PathName)
    
    Do While FileName <> vbNullString
        Dim wb As Workbook
        Set wb = Workbooks.Open(PathName & FileName)

        With wb.Worksheets("FX")  ' this is used by every statement that starts with a dot
            .Columns("W:X").Hidden = True
            .Columns("AD:AI").Hidden = True

            .Columns("U:AB").SpecialCells(xlCellTypeVisible).Interior.Color = 65535
 
            LastRow = .Columns("B").Find("B", LookAt:=xlPart).MergeArea.Row + .Columns("B").Find("B", LookAt:=xlPart).MergeArea.Rows.Count - 1

            .Range("A" & LastRow & ":A" & LastRow + 2).EntireRow.Insert

            .Range("S" & LastRow) = 20
            .Range("S" & LastRow + 1) = 50

            .Range("U" & LastRow).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.2,U$3:U$" & LastRow - 1 & ")"
            .Range("Z" & LastRow).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.2,Z$3:Z$" & LastRow - 1 & ")"
            .Range("AB" & LastRow).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.2,AB$3:AB$" & LastRow - 1 & ")"

            .Range("U" & LastRow + 1).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.5,U$3:U$" & LastRow - 1 & ")"
            .Range("Z" & LastRow + 1).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.5,Z$3:Z$" & LastRow - 1 & ")"
            .Range("AB" & LastRow + 1).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.5,AB$3:AB$" & LastRow - 1 & ")"

            .Range("U" & LastRow + 2).Formula = "=SUM(U" & LastRow & ",U" & LastRow + 1 & ")"
            .Range("Z" & LastRow + 2).Formula = "=SUM(Z" & LastRow & ",Z" & LastRow + 1 & ")"
            .Range("AB" & LastRow + 2).Formula = "=SUM(AB" & LastRow & ",AB" & LastRow + 1 & ")"
        End With
            
        FileName = Dir() ' move to next file
    Loop
End Sub

If different workbooks have different sheets you can do something like below:

Note that workbook names must contain the file extension!

Public Sub FXBuy()
    Dim PathName As String
    PathName = "H:\BASEL Reporting - Oliver's Mock\FX\"
    
    Dim FileName As String
    FileName = Dir(PathName)
    
    Do While FileName <> vbNullString
        Dim wb As Workbook
        Set wb = Workbooks.Open(PathName & FileName)
        
        Dim ws As Worksheet
        Select Case wb.Name  ' workbook names must be with file extension
            Case "FX BUY.xslm":
                Set ws = wb.Worksheets("FX BUY")
            
            Case "other workbook.xslm":
                Set ws = wb.Worksheets("other worksheet")

            Case Else:  ' for all other workbooks use this default
                Set ws = wb.Worksheets("FX")
        End Select

        With ws  ' this is used by every statement that starts with a dot
            .Columns("W:X").Hidden = True
            .Columns("AD:AI").Hidden = True

            .Columns("U:AB").SpecialCells(xlCellTypeVisible).Interior.Color = 65535
 
            LastRow = .Columns("B").Find("B", LookAt:=xlPart).MergeArea.Row + .Columns("B").Find("B", LookAt:=xlPart).MergeArea.Rows.Count - 1

            .Range("A" & LastRow & ":A" & LastRow + 2).EntireRow.Insert

            .Range("S" & LastRow) = 20
            .Range("S" & LastRow + 1) = 50

            .Range("U" & LastRow).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.2,U$3:U$" & LastRow - 1 & ")"
            .Range("Z" & LastRow).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.2,Z$3:Z$" & LastRow - 1 & ")"
            .Range("AB" & LastRow).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.2,AB$3:AB$" & LastRow - 1 & ")"

            .Range("U" & LastRow + 1).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.5,U$3:U$" & LastRow - 1 & ")"
            .Range("Z" & LastRow + 1).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.5,Z$3:Z$" & LastRow - 1 & ")"
            .Range("AB" & LastRow + 1).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.5,AB$3:AB$" & LastRow - 1 & ")"

            .Range("U" & LastRow + 2).Formula = "=SUM(U" & LastRow & ",U" & LastRow + 1 & ")"
            .Range("Z" & LastRow + 2).Formula = "=SUM(Z" & LastRow & ",Z" & LastRow + 1 & ")"
            .Range("AB" & LastRow + 2).Formula = "=SUM(AB" & LastRow & ",AB" & LastRow + 1 & ")"
        End With
            
        FileName = Dir() ' move to next file
    Loop
End Sub
user_32196130

If I understand you right, you have several Excel files in the path "H:\BASEL Reporting - Oliver's Mock\FX" and you want to run the code for each of them

Your mistake is simply that you never change the "FileName" variable.

FileName = Dir(PathName) ' Find first file in path
Do While FileName <> ""  ' As long as that file is not nothing
Set wb = Workbooks.Open(PathName & FileName) ' Open it as an Excel.Workbook

I'd recommend to not use the Dir() function at all, because it is deprecated. If you still want to use it, you have to run FileName = Dir() to get the next file name. It should work, when you do it just before the "Loop" command.

user_50782720
Related