Extract Final Row Mulitple Excel Files into Combined Workbook

  • Thread starter Thread starter londonpaul
  • Start date Start date
L

londonpaul

Hi,
Not sure if anyone can help on this, but I am completely struggling
with VBA and have been looking through threads to try and find a
solution.
I'm trying to open multiple excel files by looking at a specific
directiry file and then locate the final row and extract the data into
one spreadsheet.
I think I've managed to produce a VBA to look at multiple files but the
rest.......help !

Can anyone help?

Regards,

Paul Holmes
 
range("A65536").end(xlup).entirerow.copy destination:=
activeworkbook.close
get the next workbook
 
Thanks.
I've inserted the script but unfortunately I'm now getting an error and
it only seems to be importing one file.
I've attached where I have got to so far....
Regards,
Paul

Sub openAllfilesInALocation()
Dim i As Integer, wb As Workbook
With Application.FileSearch
..NewSearch
..LookIn = "C:\GN\Proposed"
..SearchSubFolders = False
..Filename = "*.xls"
..Execute
For i = 1 To .FoundFiles.Count
'Open each workbook
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))
'Perform the operation on the open workbook
Range("A65536").End(xlUp).Select
EntireRow.Copy
Destination = ActiveWorkbook
'Save and close the workbook
wb.Save
wb.Close
'On to the next workbook
Next i
End With
End Sub
 
can you double check these few lines?
Range("A65536").End(xlUp).Select
EntireRow.Copy
Destination = ActiveWorkbook
Click to expand...

try
Range("A65536").End(xlUp).EntireRow.Copy Destination: =
YourWorkbook.YourNextRange

One line, Destination with ":"
 
PY said:
can you double check these few lines?

try
Range("A65536").End(xlUp).EntireRow.Copy Destination: =
YourWorkbook.YourNextRange

One line, Destination with ":"
Click to expand...

Ok, thanks. It looks as though that works, but I'm now getting myself
into complete knots !
I've tried amending the text to open all the separate file to extract
the last line but i'm now getting an error on the 'end with at then end
of the text. Previous attempts have seen the script on opening the
first workbook and then giving an error424.
I'm lost!
This is from a person who can remember BASIC, just...
Help

Sub openAllfilesInALocationph()
Dim wb As Workbook
Dim i As Integer
With Application.FileSearch
..NewSearch
..LookIn = "C:\GN\Proposed"
..SearchSubFolders = False
..Filename = "*.xls"
..Execute
For mycount = 1 To .FoundFiles.Count
For i = 1 To .FoundFiles.Count
'Open each workbook
wb = Workbooks.Open(Filename:=.FoundFiles(mycount))
'Perform the operation on the open workbook
Range("A65536").End(xlUp).EntireRow.Copy
Destination:=YourWorkbook.YourNextRange
'Save and close the workbook
wb.Save
wb.Close
'On to the next workbook
Next i
End With
End Sub

Regards,
Paul
 
Back
Top