Error In Code (Aggregating Data from All Excel Workbooks in Folder)

[McArthurGDM]

Hi All,

I pulled this code from the net to aggregate data from various workbooks. I keep getting a strange error that I can't explain. Any help is appreciated!!

PS - This is helpful for Access because then I can import the data much easier with this method.

Run-Time Error 1004
Application-Defined or Object-Defined Error

This is the part of the code that isn't working, in bold and italicized.

If Range("A2").Value = "" Then
Range("A2").Select
Else
Range("A2").End(xlDown).Offset(1, 0).Select
End If
ActiveSheet.PasteSpecial (xlPasteAllExceptBorders)

Here is the full code:

Sub LoopThroughFilesInFolder()


Dim mainwb As Workbook
Dim wb As Workbook


Set mainwb = ThisWorkbook
mainwb.Activate
Sheets("Sheet1").Select
Range("A2:U51").ClearContents


Set FileSystemObj = CreateObject("Scripting.FileSystemObject")
Set FolderObj = FileSystemObj.GetFolder("C:\Users\n808037\Desktop\ TEST")


For Each fileObj In FolderObj.Files 'loop through the files


If (fileObj.Name <> "TESTmaster.xlsx" And fileObj.Name <> "~$TESTmaster.xlsx" And fileObj.Name <> "TESTmaster.xlsm" And fileObj.Name <> "~$TESTmaster.xlsm") And (FileSystemObj.GetExtensionName(fileObj.Path) = "xlsx" Or FileSystemObj.GetExtensionName(fileObj.Path) = "xlsm") Then


Application.DisplayAlerts = False
Set wb = Workbooks.Open(fileObj.Path)

'copy the results from the just opened workbook
wb.Worksheets("Template").Select
lastcell = Range("A10:U59").SpecialCells(xlCellTypeLastCell). Address
Range("A10:" & lastcell).Select


Selection.Copy

'go to the main Workbook and paste data
mainwb.Activate
Sheets("Sheet1").Select

If Range("A2").Value = "" Then
Range("A2").Select
Else
Range("A2").End(xlDown).Offset(1, 0).Select
End If
ActiveSheet.PasteSpecial (xlPasteAllExceptBorders)

wb.Activate
wb.Save
wb.Close


End If


Next fileObj


mainwb.Save


End Sub

[ranman256]

I think you mean offset(0,1) (offset 1 column)
yours says offset 1 row, then select all rows.

if you want to select all :
ActiveSheet.Range("a2").CurrentRegion.Select
???