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!

 

[Computerman]

mari_hitz,
What error mesage are you getting, and where in your code does the error message show up?

Computerman

 

[mari_hitz]

Hi Computerman! Thanks for your reply! Note that on the part of Workbooks("Source.xlsm").Worksheets("Selection") In brackets I am including on the Workbooks part the path to the excel files.
The error that I obtain is "subscript out of range". I would need to specify the path to this files since they are different and also are located in a shared drive. Thanks for your help!

 

[Fluff]

Are the two files already open?

 

[mari_hitz]

Hi Fluff, no, thanks for your reply. No, note that there is only file opened: the source one. The destination file should not be opened, only the information should be copied there. The reason behind is for the people not to be able to modify the file and break it. That is why I am trying to make the code to open the destination file, copy and paste the information, save file and close it again. Is this possible?Thanks!

 

[JumboCactuar]

I think the problem is you are not opening the destination workbook, I found this code In another thread (which I accidentally posted in), modify to what you need

Sub CopynPasteWrkBk()Dim InputFile As Workbook
Dim OutputFile As Workbook
Dim Inputpath As String
Dim Outputpath As String '


'## Open both workbooks first:
Set InputFile = ActiveWorkbook
Set OutputFile = Workbooks.Open("C:\Users\sogorman\Desktop\Test.xlsx")


'Now, copy what you want from InputFile:
InputFile.Sheets("Payroll Data").Activate
InputFile.Sheets("Payroll Data").Range("B36:O36").Copy


'Now, paste to OutputFile worksheet:
OutputFile.Sheets("Sheet1").Activate
OutputFile.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues


'Close InputFile & OutputFile:
OutputFile.Close savechanges:=True


MsgBox "Data Successfully Logged"


End Sub

 

[mari_hitz]

Hi Jumbocactuar, please note I had tried the above code and it works, the thing is that the range I am trying to copy is dynamic. So far below is the code that I am trying to copy and only copies me the content in the first cell "A2". My content is on range from A2 to K but is dynamic. Sometimes somebody can enter 3 rows sometimes 1, how can I achieve this?:

Private Sub CommandButton3_Click()Dim OutputFile As Workbook
Dim Inputpath As String
Dim Outputpath As String '




'## Open both workbooks first:
Set InputFile = ActiveWorkbook
Set OutputFile = Workbooks.Open("C:\Users\msilv1\Desktop\RawFileTesting.xlsx")


'Now, copy what you want from InputFile:
InputFile.Sheets("Selection").Activate
InputFile.Sheets("Selection").Range("A2: K" & Rows.Count).End(xlUp).Offset(1).Copy




'Now, paste to OutputFile worksheet:
OutputFile.Sheets("Sheet1").Activate
OutputFile.Sheets("Sheet1").Range("A2: k" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues




'Close InputFile & OutputFile:
OutputFile.Close savechanges:=True




MsgBox "Data Successfully Logged"




End Sub

 

[Fluff]

Try

Private Sub CommandButton3_Click()
Dim OutputFile As Workbook
Dim InputFile As Workbook
Dim Inputpath As String
Dim Outputpath As String '




'## Open both workbooks first:
Set InputFile = ActiveWorkbook
Set OutputFile = Workbooks.Open("C:\Users\msilv1\Desktop\RawFileTesting.xlsx")


'Now, copy what you want from InputFile:
With InputFile.Sheets("Selection")
   Range("A2", Range("A" & Rows.Count).End(xlUp).Offset(, 10)).Select
End With



'Now, paste to OutputFile worksheet:
OutputFile.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial paste:=xlPasteValues




'Close InputFile & OutputFile:
OutputFile.Close savechanges:=True




MsgBox "Data Successfully Logged"




End Sub

 

[mari_hitz]

Hi Fluff, please note that the code returns me error 1004: Select Method or Range class failed and when I click on Debug it highlights this portion of the code: Range("A2", Range("A" & Rows.Count).End(xlUp).Offset(, 10)).Select. Do you know why this might happen? Thanks in advance!

 

[Fluff]

Apologies, it should be

With InputFile.Sheets("Selection")
   .Range("A2", .Range("A" & Rows.Count).End(xlUp).Offset(, 10)).Copy
End With