Loop not working for all files in the folder

[ankit13]

Hello...!!!

I have a problem with my following code

Code[]
Sub Combine() Dim Fpath As String, Fname As String

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With

Fpath = "C:\Users\Final\" ' change to suit your directory
Fname = Dir(Fpath & "*.*")


With Workbooks("details.xlsx") 'MUST BE OPEN
Do While Fname <> ""
If Fname <> .Name Then
Workbooks.Open Fpath & Fname, 0
If Err.Number <> 0 Then
MsgBox ("Unable to open file " & Filename)
End If
On Error GoTo 0
Filename = Dir
'MOVE ONLY IF NOT SAVING ON CLOSE. IF SAVING, USE COPY.
Workbooks(Fname).Sheets("6.1").Copy After:=.Sheets(.Sheets.Count)
Workbooks(Fname).Close SaveChanges:=False
End If
Fname = Dir
Loop
End With

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With
End Sub
code[/]

this code loops in alternative workbook's sheets not all the workbooks of the specified folder...

I want it to loop in all the workbooks

Please help...

 

[Stiuart_W]

It's because you are calling the function "DIR" twice within your loop

Sub Combine() Dim Fpath As String, Fname As String

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With

Fpath = "C:\Users\Final\" ' change to suit your directory
Fname = Dir(Fpath & "*.*")


With Workbooks("details.xlsx") 'MUST BE OPEN
Do While Fname <> ""
If Fname <> .Name Then
Workbooks.Open Fpath & Fname, 0
If Err.Number <> 0 Then
MsgBox ("Unable to open file " & Filename)
End If
On Error GoTo 0
Filename = Dir
'MOVE ONLY IF NOT SAVING ON CLOSE. IF SAVING, USE COPY.
Workbooks(Fname).Sheets("6.1").Copy After:=.Sheets(.Sheets.Count)
Workbooks(Fname).Close SaveChanges:=False
End If
Fname = Filename
Loop
End With

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With
End Sub

I have changed Fnaem =DIR to Fname = Filename

 

[Stiuart_W]

This link has some good information on the DIR function


VBA DIR Function - How to Use in Excel

 

[ankit13]

Thanks for the help..!!!