PDA

View Full Version : Copying data from Multiple workbooks and combining in one sheet



kellymarie
12-04-2007, 07:42 AM
Hi,

I have the below code to go through all files in a workbook and copy the data into a new sheet - however I am getting a runtime error 1004??

Please help!!


Sub test()
Dim myDir As String, fn As String, ws As Worksheet
myDir = "H:\DHL Data\"
fn = Dir(myDir & "*.xls")
If fn = "" Then Exit Sub
Do While fn <> ""
Set ws = Workbooks.Open(myDir & fn).Sheets(1)
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ThisWorkbook.Sheets(1).Range("A2").Select.ActiveCell.SpecialCells(xlLastCell).Offset (1)
Workbooks(fn).Close False
fn = Dir
Loop
End Sub

stanleydgrom
12-04-2007, 08:10 AM
kellymarie,

In this line of code, remove the space in "xlLastCel l"
ThisWorkbook.Sheets(1).Range("A2").Select.ActiveCell.SpecialCells(xlLastCell).Offset (1)

Have a great day,
Stan

kellymarie
12-04-2007, 08:12 AM
Thanks for your reply...

I cant see the difference in the code?

stanleydgrom
12-04-2007, 08:16 AM
When I copied your code into the VBA Editor that code line went red.




Sub test()
Dim myDir As String, fn As String, ws As Worksheet
myDir = "H:\DHL Data\"
fn = Dir(myDir & "*.xls")
If fn = "" Then Exit Sub
Do While fn <> ""
Set ws = Workbooks.Open(myDir & fn).Sheets(1)
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ThisWorkbook.Sheets(1).Range("A2").Select.ActiveCell.SpecialCells(xlLastCell).Offset (1)
Workbooks(fn).Close False
fn = Dir
Loop
End Sub

kellymarie
12-04-2007, 08:27 AM
Oh I see....

There is no space on the actual code - I think its just the way its pasted onto here...