Pulling data from 3000 files

[flounder73]

Hi All,

I'm going crazy try to copy and paste data from 3000 files into 1 file. Is
there a way to automatically open a file, copy columns 1 -5 and then append
it to the file where I want it all placed?

Thanks in advance...

 

[Joel]

Folder = "c:\temp"
FName = Dir(Folder & "\*.xls")
Set oldbkSht = ThisWorkbook.Sheets("Sheet1")
Do While FName <> ""
OldbkLastRow = oldbkSht.Range("A" & Rows.Count).End(xlUp)
Set newbk = Workbooks.Open(Filename:=Folder & "\" & FName)
Set newbkSht = newbk.Sheets("Sheet1")
NewbkLastRow = newbkSht.Range("A" & Rows.Count).End(xlUp)
newbk.Rows("A1:E" & NewbkLastRow).Copy _
Destination:=oldbkSht.Row(OldbkLastRow + 1)
newbk.Close
FName = Dir()
Loop

 

[flounder73]

Hi Joel,

Thanks for code...however, I'm having an issue with a part of the code
"newbk.Rows("A1:E" & NewbkLastRow).Copy _
Destination:=oldbkSht.Row(OldbkLastRow + 1)"
in that it has a compilation error saying the function is not supported. I
also made a mistake in my post and I need to copy rows 1 - 5 not columns.

Sorry...thanks in advance.

 

[Norman Jones]

Hi Flounder73,

I suspect that

newbk.Rows("A1:E" & NewbkLastRow).Copy _
Destination:=oldbkSht.Row(OldbkLastRow + 1)

was intended as

newbk.Range("A1:E" & NewbkLastRow).Copy _
Destination:=oldbkSht.Row(OldbkLastRow + 1)

Ron de Bruin has an extensive set of sample
routines for copying and merging data from
multiple files at:

Copy/Paste/Merge examples
http://www.rondebruin.nl/tips.htm

Given the large number of files cited in your
question, you may wish to consider Ron's
techniques for copying data from closed files.
See:

Copy a range from closed workbooks (ADO)
http://www.rondebruin.nl/ado.htm

Ron also offers his RDBMerge Add-in which
enables the merge operation to be performed
with a few mouse clicks. |Details of the Add-in
and a download link may be found at

RDBMerge Add-in
http://www.rondebruin.nl/merge.htm

 

[Joel]

Should be
newbk.Rows("1:" & NewbkLastRow).Copy _
Destination:=oldbkSht.Rows(OldbkLastRow + 1)

The source and destination must be consistant. In this case both rows. You
can't mix Rows and Range like Norman suggested.