Vba copy and update data in Master wkbk- only copies from the first workbook right below it

Clairexcel

New Member
Joined
Mar 23, 2021
Messages
31
Office Version
  1. 2016
  2. 2010
Hello everyone,

I have a VBA code that copies all the data from all the workbooks existing in the folder and pastes them in the Masterbook (which has to be in the same folder).
It worked fine at first, thank to some adjustments/help I got from this forum, but now, it only copies the data from the workbook sitting right below it (below the master book), and does not copy the data from the other three workbooks in the folder. This is very strange.
1. The code is in the Masterbook, in a module
2. The workbooks, Master wkbk included, are in xlsb (binary format), while at first when I got this code they were xlsm (but it worked fine a xlsb too)
3. There are named ranges in the workbooks (I don't think this could be an issue)

This is the code I have, any help will be highly appreciated, as I already installed it at work place and it worked fine but now it doesn't copy all the workbooks anymore. ..Please help.
VBA Code:
Sub Update_allwkbks()
Dim wb As String
Application.ScreenUpdating = False
ThisWorkbook.Worksheets("Aggiorna le pratiche").UsedRange.Offset(1, 0).ClearContents
wb = Dir(ThisWorkbook.Path & "\*")
Do Until wb = ""
If wb <> ThisWorkbook.Name Then
Workbooks.Open ThisWorkbook.Path & "\" & wb
With Workbooks(wb).Sheets(1)
.UsedRange.Offset(1).Copy ThisWorkbook.Sheets("Aggiorna le pratiche").Cells(Rows.Count, 1).End(xlUp).Offset(1)
End With
Application.CutCopyMode = False
Workbooks(wb).Close False
End If
wb = Dir
Loop
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,776
Hi Clairexcel. I've had problems in the past with the DIR function not finding all files even though they exist so I only use the file scripting object. I've re-done your code to reflect same. You will need to change the "YourFolderName" to the actual name of your folder. Please save a copy of your wb before trialing the code. HTH. Dave
Code:
Sub Update_allwkbks()
Dim FSO As Object, FolDir As Object, FileNm As Object
On Error GoTo erfix
Application.ScreenUpdating = False
ThisWorkbook.Worksheets("Aggiorna le pratiche").UsedRange.Offset(1, 0).ClearContents
Set FSO = CreateObject("scripting.filesystemobject")
Set FolDir = FSO.GetFolder(ThisWorkbook.Path & "\YourFolderName")
For Each FileNm In FolDir.Files
If FileNm.Name <> ThisWorkbook.Name Then
Workbooks.Open Filename:=FileNm
With Workbooks(FileNm.Name).Sheets(Sheets(1))
.UsedRange.Offset(1).Copy ThisWorkbook.Sheets("Aggiorna le pratiche").Cells(Rows.Count, 1).End(xlUp).Offset(1)
End With
Application.CutCopyMode = False
Application.DisplayAlerts = False
Workbooks(FileNm.Name).Close SaveChanges:=False
End If
Next FileNm

erfix:
If Err.Number <> 0 Then
On Error GoTo 0
MsgBox "Error"
End If
Set FolDir = Nothing
Set FSO = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
570
Office Version
  1. 2010
Platform
  1. Windows
I've had problems in the past with the DIR function not finding all files even though they exist
Hi, happens only with a bad setup of the Dir parameters or scanning a network rather than a local drive.​
The drawback of FSO as it scans all files by default it is slower than VBA Dir function in particular for scanning usual files.​
As an example to search all *.xls* files on an entire volume FSO needed 2'15'', VBA Dir function less than 20" and via Windows functions less than 10" …​
 

Watch MrExcel Video

Forum statistics

Threads
1,130,080
Messages
5,639,947
Members
417,120
Latest member
Pavithra devi

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
Top