For Each wb VBA Issues

N

NHolmes4582

New Member
Joined
Dec 11, 2018
Messages
3
I have a code I've been working on to open files in a folder and see if they meet certain criteria. If so it copies it to another sheet where I am organizing the data I want. The issue I am having is when it finds a file that does not meet the criteria it just ends the code and doesn't continue to the next one. I'm ok with vba but I am no expert and I cannot figure out what to try. Please help!

Code: 
[FONT=Calibri][SIZE=3][COLOR=#000000]Sub PickLoadTest()[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    Dim myWindow AsWindow[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim Original AsWindow[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim myPath AsString[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim myFile AsString[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim wb As Workbook[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim myExtension AsString[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim WS AsWorksheet[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    Application.ScreenUpdating = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Application.DisplayStatusBar = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Application.DisplayAlerts = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Application.ErrorCheckingOptions.BackgroundChecking = False[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    Set Original =ActiveWindow[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=3][COLOR=#000000]myPath = "X:\Test Schedules\Dyno\TestReports\WGD\WGD-2701-2800"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]myFile = Dir(myPath & "*.xlsm*")[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=3][COLOR=#000000]For Each wb In Application.Workbooks[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=3][COLOR=#000000]Set wb = Workbooks.Open(FileName:=myPath & myFile)[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][SIZE=3][COLOR=#000000][FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]            SetmyWindow = ActiveWindow[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    Application.ScreenUpdating = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Application.DisplayStatusBar = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Application.DisplayAlerts = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Application.ErrorCheckingOptions.BackgroundChecking = False[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][SIZE=3][COLOR=#000000][FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][SIZE=3][COLOR=#000000][FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]            'Check tosee that this is an ISO 26867 Test Report
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                    IfRange("E22") = "ISO 26867 P" Or Range("E22") ="ISO26867P" Or Range("E22") = "ISO 26867" OrRange("E22") = "ISO 26867P" Or Range("E22") ="ISO26867" Then[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][SIZE=3][COLOR=#000000]
                   On Error Resume Next[/COLOR][/SIZE][/FONT]
[/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]                       'Copy and Move Effectiveness Data[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]                       myWindow.Activate[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       Sheets("SummaryData").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       Range("AD15:AD200").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       Selection.COPY[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]                       Original.Activate[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       Sheets("Data").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       Range("T1").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,SkipBlanks _[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                          :=False, Transpose:=True[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][SIZE=3][COLOR=#000000][FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]                       'Move Test Number to Trending Report[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       myWindow.Activate[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       Sheets("Cover").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       Range("E16").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       Selection.COPY[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=3][COLOR=#000000]                       Original.Activate[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       Sheets("Data").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       Range("A1").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                        Selection.PasteSpecialPaste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                          :=False, Transpose:=False[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]                       Rows("1:1").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       Selection.COPY[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       Rows("3:3").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       Selection.Insert Shift:=xlDown[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]                        'Copy and Move PartData[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       myWindow.Activate[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       Sheets("PartData").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       Range("B10:B25").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       Selection.COPY[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]                       Original.Activate[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       Sheets("PartData").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       Range("A1").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,SkipBlanks _[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                          :=False, Transpose:=True[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]                       Rows("1:1").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       Selection.COPY[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       Rows("7:7").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       Selection.Insert Shift:=xlDown[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]                        Application.ScreenUpdating = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                        Application.DisplayStatusBar = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                         Application.DisplayAlerts = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                        Application.ErrorCheckingOptions.BackgroundChecking = True[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][SIZE=3][COLOR=#000000][FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]                 Else
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Application.ScreenUpdating = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]     Application.DisplayStatusBar = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Application.DisplayAlerts = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Application.ErrorCheckingOptions.BackgroundChecking = True[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][SIZE=3][COLOR=#000000][FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][SIZE=3][COLOR=#000000][FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    wb.CloseSaveChanges:=False[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]                   End If

[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    wb.CloseSaveChanges:=False[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]     Original.Activate[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Application.ScreenUpdating = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Application.DisplayStatusBar = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Application.DisplayAlerts = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Application.ErrorCheckingOptions.BackgroundChecking = True[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]           Sheets("Dashboard").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]          'Recalculate, Update Sheet, Close Source Report[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            Calculate[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][SIZE=3][COLOR=#000000][FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]   Next wb[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][SIZE=3][COLOR=#000000][FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]
 
Last edited by a moderator:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
I don't understand the code. It loops through all the already open workbooks and for each one, it tries to open the same file each time. What is the logic?
 
Upvote 0
It is supposed to open all files in the selected folder then evaluate them to see if they are the correct kind of test. If it is the correct type then it copies certain things from each into my master file. Once it has copied what I need I want it to close the file and go to the next file. If it is not the correct kind of test, I want it to just close the file and move on to the next file in the folder.
 
Upvote 0
Untested. Change the start and end of the For Each loop:

Code: 
For Each wb In Application.Workbooks

Next wb
to:

Code: 
While myFile <> vbNullString
  
  myFile = Dir
Wend
and make sure you have a back slash at the end of the folder path:
Code: 
myPath = "X:\Test Schedules\Dyno\TestReports\WGD\WGD-2701-2800\"
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
another way to speed the macro .
Replies
3
Views
511
Georgiboy
Georgiboy
P
  • Solved
Formatting csv file
Replies
6
Views
530
picklefactory
P
P
  • Solved
Counter in For Each loop
Replies
2
Views
197
Possum17
P
D
  • Question
VBA optimization advice
Replies
3
Views
100
bobsan42
bobsan42
Kristylee0228
  • Question
Populating Cell based on File Name and using a List
Replies
0
Views
183
Kristylee0228
Kristylee0228

Forum statistics

Threads
1,214,979
Messages
6,122,551
Members
449,088
Latest member
davidcom

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