Copy data between workbooks

[pallar]

How should i declare this?

I want my makro to copy Say cells A1:A10 in sheet1.

Then open another workbook from path c:\myworkbook2.xlsx

Paste the data in myworkbook2, sheet1 cells B1:B10
Save myworkbook2
Close myworkbook2

And back to sheet1 from where i copied..

Can someone straight this out for me please.

 

[somnath_it2006]

Try This:

Sub test()
    
    Dim wbSource As Workbook
    Dim wbTarget As Workbook
    
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    
    ' If target file is not there
    If Dir("c:\myworkbook2.xlsx", vbNormal) = "" Then
        MsgBox "Target workbook not found as - c:\myworkbook2.xlsx", vbCritical
        Exit Sub
    End If
    
    ' Set Source file objects
    Set wbSource = ActiveWorkbook
    Set wsSource = wbSource.Sheets("Sheet1")
    
    ' Open Target file
    Set wbTarget = Workbooks.Open("c:\myworkbook2.xlsx")
    Set wsTarget = wbTarget.Sheets("sheet1")
    
    ' Copy paste the data from Source to Target
    wsSource.Range("A1:A10").Copy
    wsTarget.Range("B1").PasteSpecial (xlPasteAll)
    Application.CutCopyMode = False
    
    ' Save and close the Target file
    wbTarget.Close (True)
    
    
    ' Set all objects to nothing
    Set wbSource = Nothing
    Set wsSource = Nothing
    Set wbTarget = Nothing
    Set wsTarget = Nothing
        
End Sub

 

[pallar]

I cant get this to work.. I doublechecked but anyhow i just get the msgbox with the warning that file aint exist. But as i said i doublechecked.

 

[somnath_it2006]

If you are getting this message:

"Target workbook not found as - c:\myworkbook2.xlsx"

Means the target file where you want to paste the data, not at the c drive with name - myworkbook2.xlsx.

 

[pallar]

I just noticied why i got the fault.. My file was named myworkbook2.xlsx.xlsx

=). Works perfect now thanks alot!