Copy data from one workbook to another

[YingFa]

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?

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

 

[JLGWhiz]

This would replace the button_clck 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]

This would replace the button_clck 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]

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]

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.

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]

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.