If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
VBA copy cell to another worksheet
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 . |
#3
|
|||
|
|||
VBA copy cell to another worksheet
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 |
#4
|
|||
|
|||
VBA copy cell to another worksheet
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 mo 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 . |
#5
|
|||
|
|||
VBA copy cell to another worksheet
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 |
#6
|
|||
|
|||
VBA copy cell to another worksheet
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 . |
Thread Tools | |
Display Modes | |
|
|