Issue with workbook path in Code

[mari_hitz]

Hi all,

Please note that I am trying to copy data from a workbook to another workbook. What I am trying to achieve is that the data gets pasted to the last cell of the destination workbook.
For that I had found a code in the web that does it, however, the source and destination excel files are located in different paths and I am not being able to reference each one of them to the correct path, it continues to return error. Do you know how can I improve the below code to do that? Thanks!

Private Sub CommandButton3_Click()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("Source.xlsm").Worksheets("Selection")
  Set wsDest = Workbooks("Destination.xlsx").Worksheets("Sheet1")
    
  '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:K" & lCopyLastRow).Copy _
    wsDest.Range("A" & lDestLastRow)
End Sub

Thanks!

 

[Wanderer_geologist]

Hi

If I may say, I generally avoid to capture manually file path, this is often exposing to error oneself.
A modified version, which you can tweak if you want to run it from a third book, is using the MSOfile dialog opener to pick the file required. That make it a bit more flexible.

Sub Copy_file_to_File()


Dim SourceFile, TargetFile As Workbook
Dim wbSource_Name, wbtarget_Name As String
Dim WsSource, WsTarget As Worksheet
Dim MySource_Row, MyTarget_row As Long


'--------------------------------------------
With Application
.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
'--------------------------------------------


' Copy data from the Input/Source_Select
'I prefer generally to use the cells coordinate (Row Col Number,Col Number)
Set SourceFile = ActiveWorkbook


wbSource_Name = ActiveWorkbook.Name
Set WsSource = Worksheets("Selection")
MySource_Row = WsSource.Cells(Rows.Count, 1).End(xlUp).Row


WsSource.Range(Cells(2, 1), Cells(MySource_Row, 10)).Select
Selection.Copy




'Select the target
'THat open a dialog box with windows to select the file
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel File", "*.xlsx; *.xls"
.Show
wbtarget_Name = .SelectedItems.Item(1)
End With


' Check that something was picked up or close the macro
    If Not Len(wbtarget_Name) > 1 Then
    MsgBox "Please select a file"
    Exit Sub
    End If


Workbooks.Open (wbtarget_Name)
Set TargetFile = ActiveWorkbook
Set WsTarget = TargetFile.Worksheets("sheet1")


'Now, paste to OutputFile worksheet:
MyTarget_row = WsTarget.Cells(Rows.Count, 1).End(xlUp).Row
WsTarget.Range("A" & MyTarget_row + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues


Application.CutCopyMode = False ' Always empty the buffer


'--------------------------------------------
'Close InputFile & OutputFile:
TargetFile.Close savechanges:=True
MsgBox "Data Successfully Logged"




'--------------------------------------------
With Application
.EnableEvents = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
'--------------------------------------------


End Sub