Copy data from one workbook to another

YingFa

Board Regular
Joined
Nov 4, 2019
Messages
63
Hello,

I have this code to copy data from A2:AQ in one workbook to another but it is not working. I would like the code to coy all the data from workbook ITT to the last empty row in CW. Can I please have your help on this?
VBA Code:
Sub Button3_Click()

'Find the last used row in both sheets and copy and paste data below existing data.

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

  'Set variables for copy and destination sheets
  Set wsCopy = Workbooks("ITT.xlsm").Worksheets("FormattedData")
  Set wsDest = Workbooks(" CW.xlsx").Worksheets("Sheet")
 
     '1. Find last used row in the copy range based on data in column A
  lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
    
  '2. Find first blank row in the destination range based on data in column A
  'Offset property moves down 1 row
  lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

  '3. Copy & Paste Data
  wsCopy.Range("A2:AQ" & lCopyLastRow).Copy _
    wsDest.Range("A" & lDestLastRow)
    wsDest.Activate
    

Option Explicit

Sub OpenWorkbook()
'Open a workbook

    Workbooks.Open "C:\DS\Data\Report\CW.xlsx"
  

End Sub


Sub CloseWorkbook()
'Close a workbook

  Workbooks("CW.xlsx").Close SaveChanges:=True
  
 
End Sub
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,539
Office Version
  1. 2013
Platform
  1. Windows
This would replace the button_clck code.
VBA Code:
Sub Button3_Click()
Dim wsCopy As Worksheet
Dim wsDest As Worksheet

  'Set variables for copy and destination sheets
  Set wsCopy = Workbooks("ITT.xlsm").Worksheets("FormattedData")
  Set wsDest = Workbooks(" CW.xlsx").Worksheets("Sheet")
 
  wsCopy.UsedRange.Copy wsDest.Cells(Rows.Count, 1).End(xlUp)(2)
End Sub
 

YingFa

Board Regular
Joined
Nov 4, 2019
Messages
63
This would replace the button_clck code.
VBA Code:
Sub Button3_Click()
Dim wsCopy As Worksheet
Dim wsDest As Worksheet

  'Set variables for copy and destination sheets
  Set wsCopy = Workbooks("ITT.xlsm").Worksheets("FormattedData")
  Set wsDest = Workbooks(" CW.xlsx").Worksheets("Sheet")

  wsCopy.UsedRange.Copy wsDest.Cells(Rows.Count, 1).End(xlUp)(2)
End Sub
Hi,

Thank you for your help. I just tried the code and it gives me run time error 9, subscrit out of range. When I debug, it highlights this line Set wsDest = Workbooks("CW.xlsx").Worksheets("Sheet")
Any ide why is this?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,539
Office Version
  1. 2013
Platform
  1. Windows
That error is for a line of your original code and is telling you that you have used an invalid reference to a workbook or a sheet name. Is suspect it is the sheet name since very few people would name a sheet "Sheet" . You need to put the correct sheet name in that line and it should then work.
 

YingFa

Board Regular
Joined
Nov 4, 2019
Messages
63
That error is for a line of your original code and is telling you that you have used an invalid reference to a workbook or a sheet name. Is suspect it is the sheet name since very few people would name a sheet "Sheet" . You need to put the correct sheet name in that line and it should then work.
1603490267729.png

The name of the sheet is "Sheet" please find attached. The name of the workbook is correct as well, so I don't know why it indicates that line. I just cannot make it work.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,539
Office Version
  1. 2013
Platform
  1. Windows
View attachment 24757
The name of the sheet is "Sheet" please find attached. The name of the workbook is correct as well, so I don't know why it indicates that line. I just cannot make it work.
Could be the space in front of the CW in the workbook name. Does your workbook name have a leading space?
I have to remark that using a keyword like "Sheet" for a sheet name is not a good idea. Users should avoid using keywords in code because it leads to undesired results and can even cause files to become corrupted. Use abbreviations or compound names instead of the exact duplicate of Excel keywords. In this particular case, it probably would not cause much trouble.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,114,490
Messages
5,548,356
Members
410,828
Latest member
A9Bosv3
Top