none
vba copy cells to another workbook

    Question

  • Hi,

    I tryed to copy cells from my opened excel to another workbook but does not work, what I am doing wrong ?

    Private Sub CommandButton1_Click()
     Dim mybook
     
     Set mybook = Workbooks.Open("C:\Temp\naujas.xls")
     
     Set destrange = mybook.Worksheets("Sheet1").Range("A1:A3")
          
            ActiveSheet.Range("A1:A3").Copy Destination:=destrange
          
            mybook.Close True

    End Sub

    thank you
    aurimas

    Wednesday, August 31, 2011 2:54 PM

Answers

  • If you just need to copy the information and not formatting or anything try this.

    destrange.Worksheets("sheet1").Range("A1:A3").Value = mybook .Worksheets("sheet1").Range("A1:A3").Value

    .Copy is never as easy to uses at it should be, most of the time it’s just better to set cells equal to each other.

     

    • Marked as answer by jori5 Thursday, September 01, 2011 8:37 AM
    Wednesday, August 31, 2011 7:08 PM
  • Aurimas:

    You could set up workbook and worksheet variables to do the copy.  

    For example:

     

    Option Explicit
    
    Public Sub Test()
    ' Both Workbooks Are Open
    Dim sourcerange As Range
    Dim destrange As Range
    
    ' ************************************************
    ' Variables For Workbook1
    ' ************************************************
    Dim wkbWorkbook1 As Workbook
    Dim wksWorkbook1 As Worksheet
    
    ' ************************************************
    ' Variables For Workbook2
    ' ************************************************
    Dim wkbWorkbook2 As Workbook
    Dim wksWorkbook2 As Worksheet
    
    ' ************************************************
    ' Initialize Workbook1 Variables
    ' ************************************************
    Set wkbWorkbook1 = Workbooks("MyWorkBook1.xls")
    Set wksWorkbook1 = wkbWorkbook1.ActiveSheet
    
    ' ************************************************
    ' Initialize Workbook2 Variables
    ' ************************************************
    Set wkbWorkbook2 = Workbooks("MyWorkBook2.xls")
    Set wksWorkbook2 = wkbWorkbook2.ActiveSheet
    
    Set sourcerange = wksWorkbook1.Range("A1:A3")
    Set destrange = wksWorkbook2.Range("A1:A3")
    
    sourcerange.Copy destrange
    
    End Sub
    
    

    In case you want to Paste Values and not change the destination formatting, just substitute the following two lines for the sourcerange.Copy destrange in the above code:

    sourcerange.Copy
    wksWorkbook2.Cells(1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
    


    Regards,

     


    Rich Locus Logicwurks, LLC www.logicwurks.com





    • Marked as answer by jori5 Thursday, September 01, 2011 8:37 AM
    Wednesday, August 31, 2011 7:20 PM

All replies

  • If you just need to copy the information and not formatting or anything try this.

    destrange.Worksheets("sheet1").Range("A1:A3").Value = mybook .Worksheets("sheet1").Range("A1:A3").Value

    .Copy is never as easy to uses at it should be, most of the time it’s just better to set cells equal to each other.

     

    • Marked as answer by jori5 Thursday, September 01, 2011 8:37 AM
    Wednesday, August 31, 2011 7:08 PM
  • Aurimas:

    You could set up workbook and worksheet variables to do the copy.  

    For example:

     

    Option Explicit
    
    Public Sub Test()
    ' Both Workbooks Are Open
    Dim sourcerange As Range
    Dim destrange As Range
    
    ' ************************************************
    ' Variables For Workbook1
    ' ************************************************
    Dim wkbWorkbook1 As Workbook
    Dim wksWorkbook1 As Worksheet
    
    ' ************************************************
    ' Variables For Workbook2
    ' ************************************************
    Dim wkbWorkbook2 As Workbook
    Dim wksWorkbook2 As Worksheet
    
    ' ************************************************
    ' Initialize Workbook1 Variables
    ' ************************************************
    Set wkbWorkbook1 = Workbooks("MyWorkBook1.xls")
    Set wksWorkbook1 = wkbWorkbook1.ActiveSheet
    
    ' ************************************************
    ' Initialize Workbook2 Variables
    ' ************************************************
    Set wkbWorkbook2 = Workbooks("MyWorkBook2.xls")
    Set wksWorkbook2 = wkbWorkbook2.ActiveSheet
    
    Set sourcerange = wksWorkbook1.Range("A1:A3")
    Set destrange = wksWorkbook2.Range("A1:A3")
    
    sourcerange.Copy destrange
    
    End Sub
    
    

    In case you want to Paste Values and not change the destination formatting, just substitute the following two lines for the sourcerange.Copy destrange in the above code:

    sourcerange.Copy
    wksWorkbook2.Cells(1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
    


    Regards,

     


    Rich Locus Logicwurks, LLC www.logicwurks.com





    • Marked as answer by jori5 Thursday, September 01, 2011 8:37 AM
    Wednesday, August 31, 2011 7:20 PM
  • it worked, thank you, guys
    Thursday, September 01, 2011 6:38 AM