Copy and Paste select data to another workbook.

[tanyhart]

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

I have the following macro written, thanks to Ron de Bruin, but I kno
I am missing something in order to have the data pasted. I can get th
workbook to open, but then I get a Run-Time error '1004' pastespecia
method of range class failed. I am not sure what the code should be t
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 Trackin
Sheet.xls")
End If
Lr = LastRow(destWB.Worksheets("Tracking Sheet")) + 1
For Each smallrng In ThisWorkbook.Worksheets("Inpu
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 Yo

 

[Ron de Bruin]

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.

 

[tanyhart]

I would link the workbooks?

Is the code that I have correct or would I have to edit it?

Tany

 

[tanyhart]

I would link the workbooks?

Is the code that I have correct or would I have to edit it?

Tany

 

[Ron de Bruin]

You want to copy the cells F14,F16,F19 from the sheet Input Form to your
database workbook.

In sheet Input Form add links to the cells in a row below your data
A50 =F14
B50 =F16
C50 =F19

Now you can copy the range A50:C50 in the code

Use the code from my site now with this range and chnage the workbook/sheet names
http://www.rondebruin.nl/copy1.htm#workbook

 

[tanyhart]

I've added the links and edited the VBA code. My next question...

With the linked cells, when I change the data from the source workbook,
it automatically updates in the destination workbook. Why would I need
to use the Macro to copy and paste, besides opening up the destination
workbook?

As well, how do I get the linked data to automatically be pasted into
the last line of my desination worksheet every time new data is
transferred to the destination workbook?

Thanks for all your great help

 

[Ron de Bruin]

Read my other post good

The formula links to the cells are in the source sheet in a row below your data (hide that row when you have add the formula links)
You have now a one row range that you can copy in the dest workbook with this code

http://www.rondebruin.nl/copy1.htm#workbook

 

[tanyhart]

Sorry for being so thick.

I have it working now.

You are awsome! Thanks for all the help!!