Ask a question »

Excel /VBA - Copy data to another workbook

May 2015


Excel /VBA - Copy data to another workbook



Issue




I want to copy a portion (A1:F54) of my current worksheet that I am working on, over to another workbook stored in an online folder, without opening the latter. I want to use a button to do this. Are there any VBA codes for this purpose

Solution


Try this:
Sub CopyOpenItems() 
   ' 
   ' CopyOpenItems Macro 
   ' Copy open items to sheet. 
   ' 
   ' Keyboard Shortcut: Ctrl+Shift+O 
   ' 
   Dim wbTarget            As Workbook 'workbook where the data is to be pasted 
   Dim wbThis              As Workbook 'workbook from where the data is to copied 
   Dim strName             As String   'name of the source sheet/ target workbook 
    
   'set to the current active workbook (the source book) 
   Set wbThis = ActiveWorkbook 
    
   'get the active sheetname of the book 
   strName = ActiveSheet.Name 
    
   'open a workbook that has same name as the sheet name 
   Set wbTarget = Workbooks.Open("C:\filepath\" & strName & ".xlsx") 
    
   'select cell A1 on the target book 
   wbTarget.Range("A1").Select 
    
   'clear existing values form target book 
   wbTarget.Range("A1:M51").ClearContents 

   'activate the source book 
   wbThis.Activate 
    
   'clear any thing on clipboard to maximize available memory 
   Application.CutCopyMode = False 
    
   'copy the range from source book 
   wbThis.Range("A12:M62").Copy 
    
   'paste the data on the target book 
   wbTarget.Range("A1").PasteSpecial 
    
   'clear any thing on clipboard to maximize available memory 
   Application.CutCopyMode = False 
    
   'save the target book 
   wbTarget.Save 
    
   'close the workbook 
   wbTarget.Close 

   'activate the source book again 
   wbThis.Activate 
    
   'clear memory 
   Set wbTarget = Nothing 
   Set wbThis = Nothing 
    
End Sub


Thanks to rizvisa1 for this tip.
For unlimited offline reading, you can download this article for free in PDF format:
Excel-vba-copy-data-to-another-workbook.pdf

See also

In the same category

Published by aakai1056.
This document entitled « Excel /VBA - Copy data to another workbook » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.