How to Copy data from one excel file to another fi

Question

I am new to VBA. I am working on one file which needs to copy the data from another file which is located in different folder.

Folder 1 --> Main File---> Dashboard to Folder 2 --> Calculations---> Master File

Now the macro is to be executed from the "Dashboard" worksheet and the data is to be copied from the "Master File" and to be pasted in "Dashboard".

I tried few codes but they didn't help.

Please help me out.

Answer 1

This is a generic copy values macro I use, hopefully will put you on the right path.

Sub TransferData()
'transfer stuff from workbook 1 to workbook 2

    Dim strPath1 As String
    Dim strPath2 As String
    Dim wbkWorkbook1 As Workbook
    Dim wbkWorkbook2 As Workbook

    'define paths and filenames
    strPath1 = "C:\take from this.xlsx"
    strPath2 = "C:\put in this.xlsx"

    'open files
    Set wbkWorkbook1 = Workbooks.Open(strPath1)
    Set wbkWorkbook2 = Workbooks.Open(strPath2)

    'copy the values across
    wbkWorkbook2.Worksheets("Sheet1").Range("A1:B3").Value = _
        wbkWorkbook1.Worksheets("Sheet1").Range("A1:B3").Value

    'close the two workbooks
    wbkWorkbook1.Close (False)
    wbkWorkbook2.Close (True)

End Sub

Edit

Since you are setting workbook 1 as the workbook that is running the vba, in the above you will get errors because workbook 1 is indeed already open.

Try below code to transfer data values from 'this workbook' to workbook 2:

Sub TransferDataV2()
'transfer stuff from this workbook to workbook 2

    Dim strPath2 As String
    Dim wbkWorkbook1 As Workbook
    Dim wbkWorkbook2 As Workbook

    'define paths and filenames
    strPath2 = "C:\put in this.xlsx"

    'open files
    Set wbkWorkbook1 = ThisWorkbook '### changed this
    Set wbkWorkbook2 = Workbooks.Open(strPath2)

    'copy the values across
    '### change the sheet and range to what you need
    wbkWorkbook2.Worksheets("Sheet2").Range("A1:B3").Value = _
        wbkWorkbook1.Worksheets("Sheet1").Range("A1:B3").Value

    'close the workbook
    wbkWorkbook2.Close (True)

End Sub