Consolidating worksheets

[MAM8433]

Hello. I am trying to use a macro and VBA to consolidate files by copying the first worksheets from multiple workbooks and pasting them into one worksheet. I Googled and found the code (below) and modified it to meet my needs.

My code works successfully with smaller files. It automatically copied 20 files (45k rows total) that range in size from 10 kb to 496 kb but throws a non-descriptive “x 400” pop-up when I try to copy larger files that are 3446 kb (91k rows) and 3657 kb (116k rows). I then copy and paste these rows into my consolidated worksheet.

I stepped through the VBA code using F8 and the pop-up error message displays, “Run-time error ‘1004’ Application-defined or object-defined error.” When I isolated the large files in their own folder and start with them, the code successfully pastes the file with 91k rows into the destination file, but the second one throws the error.

Question 1: Why doesn’t the current code accommodate larger files with 91k and 116k rows of data?

Question 2: What corrections to the code will allow all files will paste via the macro?

Please know that I took a course in VBA 8 years ago and recently landed a position where I have opportunities to use it. So I'm rusty.

Sub simpleXlsMerger()

Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object

Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
Dim folderName As String
folderName = InputBox("Please enter folder address:")

'change folder path of excel files here
Set dirObj = mergeObj.Getfolder(folderName)
Set filesObj = dirObj.Files

For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
'Make sure first worksheet is selected:
bookList.Worksheets(1).Select
range("A2:X2").Select
'Ensure filtering is turned off in Row 2 column headers:
Selection.AutoFilter
'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the last column
'Also change "A" column on "A65536" to the same column as start point
range("A3:x3" & range("A1000001").End(xlUp).row).Copy
ThisWorkbook.Worksheets(1).Activate

'Do not change the following column. It's not the same column as above
range("A1000000").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
'everyObj.Close savechanges:=False
bookList.Close savechanges:=False
Next
Application.ScreenUpdating = True

End Sub

 

[cerfani]

are they all xlsx files?

range("A1000001") won't work for xls, max row on those are 65355 (i might be a little off, but it is a 5 figure number)

use Rows.Count... "A" & Rows.Count

 

[Fluff]

Try changing this

range("A3:x3" & range("A1000001").End(xlUp).row).Copy

to

Range("A3:x" & Range("A" & Rows.Count).End(xlUp).Row).Copy

 

[MAM8433]

Fluff: THANK YOU. That adjustment did the trick! All files consolidating without my intervention.

Why did your code work? When I open a file in Excel 2013, it appears that I have 1,048,576 rows in every worksheet, so I thought my code (range("A3:x3" & range("A1000001").End(xlUp).row).Copy) would work all files with a million+ rows. It worked for the smaller files, so it should have worked for the larger files.

Thanks again.

 

[Fluff]

If your file had 116,000 rows of data, then this part of your code

range("A1000001").End(xlUp).row

will return 116,000, but you are then concatenating that with this

range("A3:x3" &

which gives X3 & 116000, ie X3116000 or row 3,116,000 which is more than the 1,048,576 rows available. Hence the error

 

[MAM8433]

Fluff: many thanks for the explanation! Here's a case where my poor syntax did not generate an error, because technically, there was no error.