lleung89
04-11-2016, 07:31 AM
Hi,
I've written a vba code which essentially should find all excel files in a specific folder location and paste the data onton one master worksheet. The code seems to work until it gets to the latter part of the vba code were there is a runtime error on a specific line. It's been baffling me all morning/afternnon and I'm wondering if you guys have any thoughts as to what I'm doing wrong? The bit of the code I'm having problems with is highlighted in Red.
Sub FileSearch()
Dim iCount As Long
With Application.FileSearch
.NewSearch
.LookIn = "Folder Location"
If .Execute > 0 Then
For iCount = 1 To .FoundFiles.Count
Application.EnableEvents = False
Set importWorkbook = Workbooks.Open(Filename:=.FoundFiles(iCount))
Application.EnableEvents = True
MyName = ThisWorkbook.Name
Sheets("Master Data").Select
MyName = ActiveWorkbook.Name
Range("A2:AQ2000").Select
Selection.Copy
Windows("TEST COPY Master Invoice Queries.xls").Activate
Range("A65536").Select
Selection.End(xlUp).Select
currentrow = ActiveCell.Row
ActiveSheet.Cells(currentrow + 1, 1).Select
ActiveSheet.Cells(currentrow + 1, 6) = MyName
ActiveSheet.Paste
Set importWorkbook = Workbooks.Open(Filename:=.FoundFiles(iCount))
ActiveWorkbook.Close
Windows("TEST COPY Master Invoice Queries.xls").Activate
Sheets("Sheet1").Select
Next iCount
End If
End With
End Sub
I've written a vba code which essentially should find all excel files in a specific folder location and paste the data onton one master worksheet. The code seems to work until it gets to the latter part of the vba code were there is a runtime error on a specific line. It's been baffling me all morning/afternnon and I'm wondering if you guys have any thoughts as to what I'm doing wrong? The bit of the code I'm having problems with is highlighted in Red.
Sub FileSearch()
Dim iCount As Long
With Application.FileSearch
.NewSearch
.LookIn = "Folder Location"
If .Execute > 0 Then
For iCount = 1 To .FoundFiles.Count
Application.EnableEvents = False
Set importWorkbook = Workbooks.Open(Filename:=.FoundFiles(iCount))
Application.EnableEvents = True
MyName = ThisWorkbook.Name
Sheets("Master Data").Select
MyName = ActiveWorkbook.Name
Range("A2:AQ2000").Select
Selection.Copy
Windows("TEST COPY Master Invoice Queries.xls").Activate
Range("A65536").Select
Selection.End(xlUp).Select
currentrow = ActiveCell.Row
ActiveSheet.Cells(currentrow + 1, 1).Select
ActiveSheet.Cells(currentrow + 1, 6) = MyName
ActiveSheet.Paste
Set importWorkbook = Workbooks.Open(Filename:=.FoundFiles(iCount))
ActiveWorkbook.Close
Windows("TEST COPY Master Invoice Queries.xls").Activate
Sheets("Sheet1").Select
Next iCount
End If
End With
End Sub