Copy cells from another workbook

[gjameson via OfficeKB.com]

I have been trying to get this to work for the past few days. What I am
trying to do is:

-have a button for a macro to open select file dialog box and pick file

-then copy data from that file starting at A:9 - I:9

-the sheet calling for this information would put the information in the
first blank row in column A


The range I am copying from and to are identical A-I.


Any help would be greatly appreciated.

Gerald

 

[joel]

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

 

[gjameson via OfficeKB.com]

Joel that worked great! Thank you so much.

Gerald

 

[gjameson via OfficeKB.com]

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.

 

[gjameson via OfficeKB.com]

My sheet name is CALLS in both workbooks.

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.

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

[quoted text clipped - 27 lines]

End Sub

 

[gjameson via OfficeKB.com]

Never mind, I got this worked out. Again thanks for the help.

My sheet name is CALLS in both workbooks.

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,

[quoted text clipped - 5 lines]