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

`ThisWorkbook` refers to the workbook that the macro is in -- is that really the one you want to copy the data to? You may also want to read [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — cybernetic.nomad, Oct 04 '18 at 14:23
I want to open a workbook using the path of the file which in the sheet 'Instructions', copy the data from the sheet 'ABC' in the workbook I just opened, and paste it in the original workbook under 'ABC DUMP'. — eenz, Oct 04 '18 at 14:44

score 1 · Answer 1 · answered Oct 04 '18 at 14:47

If you define a variable as a string and then set it to equal your filename ('Instructions!'$A$2) you can then use this variable in the workbooks.open function.

Your workbooks.open function also requires a pathname for this workbook; therefore define another variable for your pathname and you should be able to use:

Workbooks.Open Filename:=PathName & Filename

score 1 · Accepted Answer · answered Oct 04 '18 at 14:48

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

Create a macro that copy and pastes data

2 Answers2