Create a macro that copy and pastes data

Question

I'm trying to create a macro that opens a file and copy and pastes data from that file into a new excel sheet.

Problem is, the file is updated every month. So I have a sheet on excel where I have copy pasted the path to that file (Instructions and its in cell A2). I want to know how I can adjust my code to open that file, copy its data and close the file. I also want to create a button that I can press to run the macro.

This is my code so far:

Sub ImportData_Click()
'open the source workbook and select the source sheet
Workbooks.Open Filename:="'Instructions!'$A$2"   'this is the part of the 
code that I'm having trouble with
Sheets("ABC").Select

' copy the source range
Sheets("ABC").Range("C:AI").Select
Selection.Copy

' select current workbook and paste the values 
ThisWorkbook.Activate
Sheets("ABC DUMP").Select
Sheets("ABC DUMP").Range("A:").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'close the source workbook
Windows("'Instructions!'$A$2").Activate    

ActiveWorkbook.Close

End Sub

Answer 1

You have several minor syntax errors. With A2 data like:

C:\TestFolder\ABC.xls

this appears to work just fine:

Sub ImportData_Click()
    'open the source workbook and select the source
    Dim wb As Workbook

    Workbooks.Open Filename:=Sheets("Instructions").Range("$A$2").Value
    Set wb = ActiveWorkbook
    Sheets("ABC").Select

    ' copy the source range
    Sheets("ABC").Range("C:AI").Select
    Selection.Copy

    ' select current workbook and paste the values
    ThisWorkbook.Activate
    Sheets("ABC DUMP").Select
    Sheets("ABC DUMP").Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

    'close the source workbook

    wb.Close

End Sub

This can be re-coded to avoid Select