VBA copy cell to another worksheet

Hello, I am trying to copy text information from one worksheet to
another.
I open only the first sheet then I have a comand button by where I
want to say transfer the values.

La macro give an error at  ObjWorshett.Cells(Riga, 1).text =
oDO.GetText
Errore di run-time '1004'.
Errore definito dall'applicazione o dall'oggetto.
Thanks in advance!
Franco



Dim oDO As New DataObject
oDO.SetText [B2].Value
oDO.PutInClipboard

Dim ObjWorshett As Worksheet
Dim strNomeFile As String

strNomeFile = "\\srv01\Dp\ANTONELLA\sblocco.xls"
Set ObjWorshett = Application.Workbooks.Open(strNomeFile, False,
True).Sheets(1)
 UltimaRiga = ObjWorshett.Range("A65356").End(xlUp).Row
    Riga = UltimaRiga + 1

    oDO.GetFromClipboard
     ObjWorshett.Cells(Riga, 1).text = oDO.GetText
    Set oDO = Nothing

ObjWorshett.Application.ActiveWorkbook.Close
0
franco
3/5/2010 5:02:44 PM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
1782 Views

Similar Articles

[PageSpeed] 20

I hope I have understood your needs and that this helps you.  I don't believe 
you need to use the clipboard for this.  You can copy the value in cell B2 on 
the active sheet (the one with the command button on it) and place it into 
the other workbook.

Also, you were opening the sblocco.xls workbook as Read Only which means you 
could not save the change you make to it.  I have changed that.

Sub CopyCellValue()
  Dim ObjWorshett As Worksheet
  Dim strNomeFile As String
  Dim UltimaRiga As Long
  Dim Riga As Long
 
'  new variables used  
  Dim objWorkbook As Workbook
  Dim valueFromThisWorkbook As Variant
  
  'get the value in Cell B2 on the sheet
  'that is active in this workbook
  valueFromThisWorkbook = ActiveSheet.Range("B2").Value
  
  strNomeFile = "\\srv01\Dp\ANTONELLA\sblocco.xls"
  
  'open the other workbook, do not update links, _
   do NOT open as read only
  Set objWorkbook = Workbooks.Open(strNomeFile, False, False)
  Set ObjWorshett = objWorkbook.Sheets(1)
  
  UltimaRiga = ObjWorshett.Range("A" & Rows.Count).End(xlUp).Row
  Riga = UltimaRiga + 1
  'if you do not need UltimaRiga later, you can rewrite those
  'two statements as one:
  ' Riga = ObjWorshett.Range("A" & Rows.Count).End(xlUp).Row + 1
  
  ObjWorshett.Cells(Riga, 1).Value = valueFromThisWorkbook
  Set ObjWorshett = Nothing
  objWorkbook.Close True ' close and save changes
  Set objWorkbook = Nothing
End Sub


"franco monte" wrote:

> Hello, I am trying to copy text information from one worksheet to
> another.
> I open only the first sheet then I have a comand button by where I
> want to say transfer the values.
> 
> La macro give an error at  ObjWorshett.Cells(Riga, 1).text =
> oDO.GetText
> Errore di run-time '1004'.
> Errore definito dall'applicazione o dall'oggetto.
> Thanks in advance!
> Franco
> 
> 
> 
> Dim oDO As New DataObject
> oDO.SetText [B2].Value
> oDO.PutInClipboard
> 
> Dim ObjWorshett As Worksheet
> Dim strNomeFile As String
> 
> strNomeFile = "\\srv01\Dp\ANTONELLA\sblocco.xls"
> Set ObjWorshett = Application.Workbooks.Open(strNomeFile, False,
> True).Sheets(1)
>  UltimaRiga = ObjWorshett.Range("A65356").End(xlUp).Row
>     Riga = UltimaRiga + 1
> 
>     oDO.GetFromClipboard
>      ObjWorshett.Cells(Riga, 1).text = oDO.GetText
>     Set oDO = Nothing
> 
> ObjWorshett.Application.ActiveWorkbook.Close
> .
> 
0
Utf
3/5/2010 8:22:22 PM
Thanks JLatham, it's exactly that I want!
But on the line --> Set objWorkbook = Workbooks.Open(strNomeFile,
False, False)
I have the same error:
Errore di run-time '1004'.
Errore definito dall'applicazione o dall'oggetto.
I'm searching for on the net but Your help is appreciated!
Thanks again
0
franco
3/6/2010 3:27:45 PM
It works properly for me under several tests.  So we need to focus in on what 
part of the command is not working properly.

Set up some test code and see if it works:

Sub TestFileOpen()
  Workbooks.Open("\\srv01\Dp\ANTONELLA\sblocco.xls")
End Sub

If that does not work properly, check to be certain that the path to the 
file is correct.

If that does work, then change the code and test again:
Sub TestFileOpen()
  Workbooks.Open("\\srv01\Dp\ANTONELLA\sblocco.xls", False, False)
End Sub

And if that also works properly, test a little more:
Sub TestFileOpen()
  Dim strNomeFile As String
  
  strNomeFile = "\\srv01\Dp\ANTONELLA\sblocco.xls"
  Workbooks.Open(strNomeFile, False, False)
End Sub

and once more, if that succeeds, move on to another test
Sub TestFileOpen()
  Dim objWorkbook As Workbook
  Dim strNomeFile As String
  
  strNomeFile = "\\srv01\Dp\ANTONELLA\sblocco.xls"
  Workbooks.Open(strNomeFile, False, False)
  Set objWorkbook = Workbooks(ActiveWorkbook.Name)
  ThisWorkbook.Activate
End Sub


"franco monte" wrote:

> Thanks JLatham, it's exactly that I want!
> But on the line --> Set objWorkbook = Workbooks.Open(strNomeFile,
> False, False)
> I have the same error:
> Errore di run-time '1004'.
> Errore definito dall'applicazione o dall'oggetto.
> I'm searching for on the net but Your help is appreciated!
> Thanks again
> .
> 
0
Utf
3/7/2010 2:16:01 PM
Thanks JLatham, now it's work correct!  Thanks again!!!!

Private Sub Memorizza_Click()
Dim ObjWorshett As Worksheet
  Dim strNomeFile As String
  Dim UltimaRiga As Long
  Dim Riga As Long

'  new variables used
  Dim objWorkbook As Workbook
  Dim valueFromThisWorkbook As Variant

    Application.ScreenUpdating = False ' Lavora in background

  valueB2 = ActiveSheet.Range("B2").Value 'Agente
 ' ....

  strNomeFile = "\\srv01\Dp\ANTONELLA\Lista sblocco ordini.xls"

  'open the other workbook, do not update links, _
   do NOT open as read only
  Set objWorkbook = Workbooks.Open(strNomeFile, False, False)
  Set ObjWorshett = objWorkbook.Sheets(1)

  UltimaRiga = ObjWorshett.Range("A" & Rows.Count).End(xlUp).Row
  Riga = UltimaRiga + 1

  ObjWorshett.Cells(Riga, 1).Value = valueB2 'Agente
' ....

  Set ObjWorshett = Nothing
  objWorkbook.Close True ' close and save changes
  Set objWorkbook = Nothing
  Application.ScreenUpdating = True ' Fine Lavora in background
End Sub
0
franco
3/7/2010 4:24:37 PM
Good to hear that.  Glad I could help.

"franco monte" wrote:

> Thanks JLatham, now it's work correct!  Thanks again!!!!
> 
> Private Sub Memorizza_Click()
> Dim ObjWorshett As Worksheet
>   Dim strNomeFile As String
>   Dim UltimaRiga As Long
>   Dim Riga As Long
> 
> '  new variables used
>   Dim objWorkbook As Workbook
>   Dim valueFromThisWorkbook As Variant
> 
>     Application.ScreenUpdating = False ' Lavora in background
> 
>   valueB2 = ActiveSheet.Range("B2").Value 'Agente
>  ' ....
> 
>   strNomeFile = "\\srv01\Dp\ANTONELLA\Lista sblocco ordini.xls"
> 
>   'open the other workbook, do not update links, _
>    do NOT open as read only
>   Set objWorkbook = Workbooks.Open(strNomeFile, False, False)
>   Set ObjWorshett = objWorkbook.Sheets(1)
> 
>   UltimaRiga = ObjWorshett.Range("A" & Rows.Count).End(xlUp).Row
>   Riga = UltimaRiga + 1
> 
>   ObjWorshett.Cells(Riga, 1).Value = valueB2 'Agente
> ' ....
> 
>   Set ObjWorshett = Nothing
>   objWorkbook.Close True ' close and save changes
>   Set objWorkbook = Nothing
>   Application.ScreenUpdating = True ' Fine Lavora in background
> End Sub
> .
> 
0
Utf
3/7/2010 8:21:01 PM
Reply:

Similar Artilces:

Prevent clicking on a cell
I want to run the code below to prevent a range of cells from being selected if the Range("Q7") = 1. I have all cells on the worksheet locked but the user must be able to click on the locked cells to trigger a userform so I have to check Select Locked Cells. So is there any way make the Range("B5:C5") unselectable? If Range("Q7") = 1 Then Range("B5:C5").Locked = True End If Hi, >So is there any way make the > Range("B5:C5") unselectable? No but you can stop them staying there. Private Sub Worksheet_...

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

Can I copy radio buttons so that the second set is independent of.
I have a group of four radio buttons that all point to one cell. My goal is to create a second set that looks identical to the first but which points to a second cell. This is for a questionaire that will have many questions with the same four answer values. I want to be able to quickly create 100 button groups that will each update a separate cell for scoring of the questionaire. There are optionbuttons on the Control toolbox toolbar and there are optionbuttons on the Forms toolbar. Each has different behaviors. If I had to use lots, I'd use the Forms version. Here's a pos...

double worksheets?
A co-worker was working in a regularly-used workbook. Suddenly a second, seemingly identical workbook opened; the two were named *.xls:1 and *.xls:2 respectively. A week ago, a similar situation happened with the same workbook, except the duplication occured upon opening, rather than after it was already open. At that time, when we closed one of the "versions" the colon-number additional extention disappeared. But after closing the remaining document and reopening, the duplication appeared again upon opening. Can anyone tell us what this means? Did she accidentally hit a comman...

Worksheet Auto update
I need to find a way to automate a process. Is there a way to automatically replace the content of a worksheet with the content of another one? Every morning I get a sales report in excel for the previous day sales. I save it in a folder and then I do a pivot table on this sheet to determine sales by product category for example. The following day, I open the previous day file, replace the sales report with the new one and then refresh my pivot table. Is there a way to have my sales report update anytime I get a new sales report? To be more clear I have a workbook with two tabs: Pivot...

How do I extend a underline across an entire cell?
When working on a financial statement, I was curious how to 1. Have a line extend across an entire cell even if the number is only 2-3 digits and 2. How to apply a double line under a number without using the = sign in the following cell? Hi Lindsay Look on the formatting toolbar for Borders -- Regards Ron de Bruin http://www.rondebruin.nl "Lindsay" <Lindsay@discussions.microsoft.com> wrote in message news:F4C9ED6C-7F2D-4277-86CC-6FA46D315DA5@microsoft.com... > When working on a financial statement, I was curious how to 1. Have a line > extend across an entire ce...

Copy Toolbar
Can someone tell me if there is a way to copy a toolbar I created on my computer to another computer? Thanks for your help! --- Message posted from http://www.ExcelForum.com/ The toolbar settings are in the *.xlb file. -- Regards, Peo Sjoblom "HelpMe >" <<HelpMe.zmcsh@excelforum-nospam.com> wrote in message news:HelpMe.zmcsh@excelforum-nospam.com... > Can someone tell me if there is a way to copy a toolbar I created on my > computer to another computer? Thanks for your help! > > > --- > Message posted from http://www.ExcelForum.com/ > You...

Separating Date and Time in a cell
I have a column of cells in the format "11/01/02 06:21". I would like to separate the text into 2 cells - one with the date and the other with the time. My attempts with LEFT and RIGHT have been unsuccesful. Thanks for your help Sameer --- Message posted from http://www.ExcelForum.com/ For the date use =INT(A1) replace A1 with the first cell of your range for time =MOD(A1,1) you probably have to reformat the first to mm/dd/yy (or whatever the setting is) and hh:mm Note that you can do this by just using format but if you want to compare to other cells with just pure d...

How do I copy the result of a check box into another check box?
I'm trying to copy the result of a "check box form field" into another check box. For example: if I check (or uncheck) one box in a form, another box later in the protected form will also be checked (or unchecked) , much like the ref + F9 command for the text form field. Is this possible? Thanks. This cannot be done without macros. If your project will allow macros - see http://word.mvps.org/faqs/tblsfldsfms/ExclusiveFmFldChbxs.htm -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> G...

cell contents revert to 0 when i click on the next cell
I put a number into a cell click on the next cell and the first cell reverts to 0. If I format to number with 2 decimal places it will be ok but when I try to take out decimal places it goes back to zero, Help please You haven't said what number you are trying to put into the cell, but I suspect that the number is less than 0.5. A quick test shows that if you set the cell to no decimal places then enter a number less than 0.5 it is displayed 'rounded down' so it will show as zero, if it's 0.5 or above it displays as 1. If you need to put numbers less than 0.5 into youe c...

Dynamic Range Selection Using VBA
What I'm trying to accomplish is to be able to run a procedure that selects a range based on a number provided in another cell. For example; if the number 10 is in cell A1, then cells A20:A30 would be selected when I run the macro. If the number 6 is provided, then cells A20:A26 would be selected. Not sure where to start, so any help is appreciated. this may do what you want range("A20").Resize(range("A1").Value+1).select -- Gary Keramidas Excel 2003 "TEK" <TEK@discussions.microsoft.com> wrote in message news:DA9FFF99-FC28-...

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF($G21<>"",($H20-$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

creating a spredsheet and log the info into another spredsheet
hello, I have a excel sheet that is printed out and a cashier manual enters information, invoice number, invoice amount, cash amount, check amount, amex amount.... I would like to have the cashier input this information on her PC and print a copy to go along with the deposit and at the same time log the information into a google excel document I created. Any ideas how this can be done? ...

Copy HYPERLINKS
I have about 200 entries in col a Some of these entries are hyperlinks to other locations I would like a macro to copy ONLY the hyperlinks from Col a to Col b Currently the hyperlinked items are interspaced over the entire col a, I would like to have all the hyperlinks moved to Col B one under the other with no spaces. Thanks Are they =HYPERLINK functions or Inserted hyperlinks?? -- Gary''s Student - gsnu200750 "pcor" wrote: > I have about 200 entries in col a > Some of these entries are hyperlinks to other locations > I would like a macro to copy ONLY the...

Automatic changes in cells
Hi for some reason I now have to save my work for any formlas etc to change when I update a worsheet, how can I stop this as it is a pain and sometimes I need to do changes to see how they work before saving the work. Many thanks Click on Tools | Options | Calculation tab and set to Automatic calculation, as it is probably set to Manual. You can press F9 to force a recalculation under a manual setting. Make sure you save the file with the Automatic setting, to avoid it happening next time. Hope this helps. Pete On Feb 1, 11:42=A0am, Office 2004 Test Drive User <heepenm...@yahoo.co.u...

cell colour change when set markers are reached
i need to get a cell to change colour when markers are reached eg a qualification lasts 12 months. what i want to do is have the cell change from yellow to orange to red as the expiry date gets closer. If column A contains expiry dates then select column A, Formats>Conditional Formatting>formula1: =DATEDIF(TODAY(),A1,"m")<1 red for 1 month Click Add button, formula2: =DATEDIF(TODAY(),A1,"m")<2 orange for 2 month Click Add button, formula3: =DATEDIF(TODAY(),A1,"m")<3 yellow for 3 month Adjust number of months as you like! Regards,...

how can I do a lookup into another sheet?
how can I do a lookup into another sheet? thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Try something like =VLOOKUP(123,Sheet2!A1:B10,2,FALSE) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "melawaisi" <melawaisi.ws67m@excelforum-nospam.com> wrote in message news:melawaisi.ws67m@excelforum-nospam.com... > > how can I do a lookup into another sheet? > thanks > > > ------...

Carbon Copy of a message
when ever I get an email from 2 specific users the message always Carbon Copies itself and I cant figure out why. So I end up with 2 of the same messages. Any help would rock Thanx ...

Calculating on alphabetic cell content
Hi, A selection of 4 different letters in a column representing different values to be used in a formula shall be run through. The calculated result of each cell in the column shall be placed in the cell next to the read one that holds the letter. Thanks in advance. Hi i think you're after the COUNTIF function with your column of letters in A1:A100 and the letter you're interested in in C1 then in D1 =COUNTIF(A1:A100,C1) this will count the number of times the value in C1 occurs in your range. If this isn't what you're after, could you type out a few examples of your ...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

Duplicating one Field from One table to Another
Hi - I have two tables - one position, one personnel - which has a 1-to-many relationship (1 position record to many personnel records). The department had a new requirement which made it necessary to change some coding (I inherited this). I'm using tab forms so that when a position is pulled up, you can click on the tab that has the personnel information (if there is any). There is a button on the Personnel form that allows the user to add a new Personnel record. Since I am using an Auto-number field in the Position table (which doubles as the PK) the functionality is fine. Wh...

Sorting Cells by Colors
Hi all, Is it possible to write a VBA code to sort excel cells by colors, and the followed by other criterias, as in the normal sort? Thank you in advance. Hi SwiftCode, See Chip Pearson's Sorting By Color page at: http://www.cpearson.com/excel/SortByColor.htm --- Regards, Norman "swiftcode" <swiftcode@discussions.microsoft.com> wrote in message news:FC1550A7-A8DD-4EC0-B171-F1DB4373C35C@microsoft.com... > Hi all, > > Is it possible to write a VBA code to sort excel cells by colors, and the > followed by other criterias, as in the normal sort?...

Textbox fomatting value based on another textbox
I have two text boxes on a form. One is a value that can be changed by the user. The second is the value 1 - textbox1. I need everthing to be in %. For example, in textbox1 the user could type 75 and it would automatically be recognized as 75% and textbox2's value would calculate to be 25%. Everytime I try textbox1's value is = to say 7500%. Any help is appreciated. Cheers, Job Maybe something like: Option Explicit Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim TB1Val As Double With Me.TextBox1 If IsNumeric(.Value) Then TB1Va...