Macro code help - Copy columns to other workbook

[KelemvorX]

Hi Guys,

Workbooks:
File 1 - Interviewer_Response_Data-Data_Splitter.xlsm - Contains Macro
File 2 - Huongs_Dashboard.xls - Data to be pasted here

Both contain a single worksheet called Raw_Data

Actions needed:
1. Macro running in File 1 needs to open File 2
2. Copy certain columns of data (not headers) from Raw_Data tab.
3. Paste in File 2 on Raw_Data tab under last data cell
4. Save and close File 2

This is the code I have pieced together so far as I am new to this, but it opens File 2 and then does not do anything else, some help would be appreciated

Sub OpenWorkbook()

Workbooks.Open "XXXXXXX"

End Sub

Sub Copy_Paste_Below_Last_Cell()
'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("Interviewer_Response_Data-Data_Splitter.xlsm").Worksheets("Raw_Data")
Set wsDest = Workbooks("Huongs_Dashboard.xlsx").Worksheets("Raw_Data")

'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("A:C,E:F,J:J,M:P,R:T,V:V" & lCopyLastRow).Copy _
wsDest.Range("A" & lDestLastRow)

End Sub

Sub CloseWorkbook()

Workbooks("Huongs_Dashboard.xls").Close SaveChanges:=True

End Sub

Thanks
Kelem~

[Mumps1]

Try:

Please Login or Register  to view this content.

[KelemvorX]

Hi Mumps1,

Thank you for reviewing, unfortunately it seems to still have issues functioning and throws up an error.

Seems there is issues with locating the open workbook.

I have attached the image of error and debugger.

Thanks
Kelem~

[Mumps1]

Try using the full path. Change to suit your needs.

Please Login or Register  to view this content.

[KelemvorX]

Hi Mumps1,

Thank you for your help an apologies for the delayed response!

The above didnt work, however I replaced with Windows("Huongs_Dashboard.xlsx").Activate and it works perfectly!

As the initial call opens the workbook.

Thank you so much for your help!

Thanks
Kelem~

[Mumps1]

You are very welcome.