Ask a question »

Excel /VBA - Copy data to another workbook

February 2016

Excel /VBA - Copy data to another workbook


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


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 
   'clear existing values form target book 

   'activate the source book 
   'clear any thing on clipboard to maximize available memory 
   Application.CutCopyMode = False 
   'copy the range from source book 
   'paste the data on the target book 
   'clear any thing on clipboard to maximize available memory 
   Application.CutCopyMode = False 
   'save the target book 
   'close the workbook 

   'activate the source book again 
   '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:

See also

In the same category

Published by aakai1056.
This document entitled « Excel /VBA - Copy data to another workbook » from CCM ( 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.