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
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