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

--
Message posted via http://www.officekb.com

You didn't specify which sheets in the workbook you are using so I
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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=185731

Excel Live Chat

Joel that worked great! Thank you so much.

Gerald

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

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

My sheet name is CALLS in both workbooks.

gjameson wrote:
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

--
Message posted via http://www.officekb.com

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

gjameson wrote:
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]

End Sub

--
Message posted via http://www.officekb.com