Hi tanyhart

Create links to the cells Range("F14,F16,F19") in a row below your data
You can hide that row if you want

Then copy that one row range

Note from my site

Tip: Use a row below your data (if the range have separate areas) with links to cells you want (=C3 in A50, =G15 in B50, ...).
You can hide this row if you want and copy a range like A50:Z50 for example with one of the values copy examples.


--
Regards Ron De Bruin
http://www.rondebruin.nl



"tanyhart" wrote in message
...

I would like to copy data from a worksheet; F14, F16, F19 and copy them
to another workbook where they would be pasted in a select order on one
line.

I have the following macro written, thanks to Ron de Bruin, but I know
I am missing something in order to have the data pasted. I can get the
workbook to open, but then I get a Run-Time error '1004' pastespecial
method of range class failed. I am not sure what the code should be to
paste the selected data.

Sub SendToTracking()
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
Function bIsBookOpen(ByRef szBookName As String) As Boolean
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function

Sub copy_to_another_workbook()
Dim smallrng As Range
Dim destrange As Range
Dim destWB As Workbook
Dim Lr As Long

Application.ScreenUpdating = False
If bIsBookOpen("P&WM Estimate Tracking Sheet.xls") Then
Set destWB = Workbooks("P&WM Estimate Tracking Sheet.xls")
Else
Set destWB = Workbooks.Open("N:\Estimate Sheet\P&WM Estimate Tracking
Sheet.xls")
End If
Lr = LastRow(destWB.Worksheets("Tracking Sheet")) + 1
For Each smallrng In ThisWorkbook.Worksheets("Input
Form").Range("F14,F16,F19").Areas
Set destrange = destWB.Worksheets("Tracking Sheet").Range("A" & Lr)
smallrng.Copy destrange
Next smallrng
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True
End Sub

Any help would be greatly appreciated.

Thank You


--
tanyhart
------------------------------------------------------------------------
tanyhart's Profile: http://www.excelforum.com/member.php...o&userid=35148
View this thread: http://www.excelforum.com/showthread...hreadid=551008