One more question. Is there anyway to paste only the data from the worksheet?
The worksheet I am copying from contains a formula and I only want that data,
right now it shows up as an error because of the ref.

joel wrote:
You didn't specify which sheets in the workbook you are using so
chose the first tab in each workbook.

Sub AddData()

With ThisWorkbook.Sheets(1)
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Newrow = LastRow + 1
Set DestLocation = .Range("A" & Newrow)
End With

fileToOpen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If fileToOpen = False Then
MsgBox ("cannot Open file - Exiting Macro")
Exit Sub
End If

Set bk = Workbooks.Open(Filename:=fileToOpen)
Set sht = bk.Sheets(1)

With sht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Set Copyrange = .Range("A9:I" & LastRow)
Copyrange.Copy _
Destination:=DestLocation
End With

bk.Close savechanges:=False

End Sub


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/201003/1