Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste select data to another workbook.
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 -- tanyhar ----------------------------------------------------------------------- tanyhart's Profile: http://www.excelforum.com/member.php...fo&userid=3514 View this thread: http://www.excelforum.com/showthread.php?threadid=55100 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste select data to another workbook.
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste select data to another workbook.
I would link the workbooks? Is the code that I have correct or would I have to edit it? Tany -- tanyhar ----------------------------------------------------------------------- tanyhart's Profile: http://www.excelforum.com/member.php...fo&userid=3514 View this thread: http://www.excelforum.com/showthread.php?threadid=55100 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste select data to another workbook.
I would link the workbooks? Is the code that I have correct or would I have to edit it? Tany -- tanyhar ----------------------------------------------------------------------- tanyhart's Profile: http://www.excelforum.com/member.php...fo&userid=3514 View this thread: http://www.excelforum.com/showthread.php?threadid=55100 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste select data to another workbook.
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 -- Regards Ron De Bruin http://www.rondebruin.nl "tanyhart" wrote in message ... I would link the workbooks? Is the code that I have correct or would I have to edit it? Tanya -- tanyhart ------------------------------------------------------------------------ tanyhart's Profile: http://www.excelforum.com/member.php...o&userid=35148 View this thread: http://www.excelforum.com/showthread...hreadid=551008 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste select data to another workbook.
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:) -- tanyhart ------------------------------------------------------------------------ tanyhart's Profile: http://www.excelforum.com/member.php...o&userid=35148 View this thread: http://www.excelforum.com/showthread...hreadid=551008 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste select data to another workbook.
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 -- Regards Ron De Bruin http://www.rondebruin.nl "tanyhart" wrote in message ... 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:) -- tanyhart ------------------------------------------------------------------------ tanyhart's Profile: http://www.excelforum.com/member.php...o&userid=35148 View this thread: http://www.excelforum.com/showthread...hreadid=551008 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste select data to another workbook.
Sorry for being so thick. I have it working now. You are awsome! Thanks for all the help!! -- tanyhart ------------------------------------------------------------------------ tanyhart's Profile: http://www.excelforum.com/member.php...o&userid=35148 View this thread: http://www.excelforum.com/showthread...hreadid=551008 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and paste data from one sheet to another in a single workbook | Excel Programming | |||
Selecting data from 1 workbook to copy and paste to a 2nd workbook | Excel Programming | |||
copy data from one worksheet and paste into another workbook | Excel Programming | |||
Copy worksheet and paste it in New workbook, all data except Formulas | Excel Programming | |||
Select All and copy and paste | Excel Programming |