CODEWITHSUNDEEP
vbaexcel
user4946547
user4946547

Reputation:

VBA Stuck in nested loop

I'm writing a code that compares the first few characters of a workbook (fromWB) name (tempName) with the name of a worksheet (sheetName). If they match, then the data from the file is copied into the worksheet (ws). I'm trying to get my code to iterate through the folder of files and through the workbook (fromWB) with multiple worksheets but the code is stuck in the do while loop (iterating through the worksheets), and I'm not sure how to fix it. It stops copying and pasting into the worksheet because tempName isn't changing since the code is not looping through the folder of files. I debugged the code and I'm not getting any errors. Any help would be appreciated!!

EDIT: Would two nested For Each loops work?

Sub Import(Optional sPath As Variant)
Dim SelectFolder As FileDialog
Dim fromWB As Workbook
Dim toWB As Workbook
Dim ws As Worksheet
Dim fileName As String
Dim sheetName As String
Dim tempName As String


'Application.ScreenUpdating = False
'Application.DisplayAlerts = False
'Application.EnableEvents = False

'Retrieve Target Folder Path From User
Set SelectFolder = Application.FileDialog(msoFileDialogFolderPicker)

With SelectFolder
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    If .Show <> -1 Then GoTo NextCode
    sPath = .SelectedItems(1) & "\"
End With

'In Case of Cancel
NextCode:
sPath = sPath
If sPath = "" Then GoTo ResetSettings

sFile = Dir(sPath & "*xlsx")
Workbooks("Temp.xlsx").Activate
Set toWB = ActiveWorkbook

Do While sFile <> ""
    Set fromWB = Workbooks.Open(sPath & sFile)
    tempName = Left(sFile, 3)
    Range("A1:B10").Select
    Selection.Copy

    For Each ws In toWB.Worksheets
        Windows("Temp.xlsx").Activate
        sheetName = ActiveSheet.Name
        If sheetName = tempName Then
            ws.Activate
            Range("A4").PasteSpecial xlPasteAllUsingSourceTheme
        End If
        ws.Activate
    Next ws
sFile = Dir()
Loop


ResetSettings:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Upvotes: 0

Views: 297

Answers (1)

tea_pea
tea_pea

Reputation: 1542

I don't think there is a problem with your for each loop. Seems to me it will be getting stuck in the do while loop.

You are not changing sFile anywhere in the loop so it will never break.

Do While sFile <> ""

Loop

EDIT:

Sub LoopThroughFiles()
    Dim MyObj As Object, MySource As Object, file As Variant
    file = Dir("c:\testfolder\")
    While (file <> "")
      'code here
      file = Dir
    Wend
End Sub

From: Loop through files in a folder using VBA?

Upvotes: 1

Related Questions