VBA - copying & pasting data using file path

[Fredek800]

Firstly hello to all of you! Please be kind, I'm a total VBA noob!

I would like to create a macro in a workbook called "Data". The macro should pull in data from a closed workbook called "Data Source" and into an existing sheet in the "Data" workbook called DataSheet.

I would like the file path of the "Source Data" to be written in cell A1 of Sheet1 in the "Data" file (e.g. C:\Users\Fred\Desktop\Data Source.xlsm). This is so that I am easily able to change the file path of the Data Source spreadsheet.

Would someone be kind enough to help me write this Macro?

[Bob Phillips]

Public Sub CopyData()
Dim ws As Worksheet
Dim filename As String

    With Application.FileDialog(msoFileDialogOpen)
    
        .AllowMultiSelect = False
    
        If .Show Then
        
            filename = .SelectedItems(1)

            Set ws = Workbooks.Open(filename:=filename)
            ws.Worksheets(1).UsedRange.Copy
            Workbooks("Data").Worksheets("DataSheet").Range("A1").Copy
            ws.Close SaveChanges:=False
            Set ws = Nothing
        End If
    End With
End Sub

[Fredek800]

Thank you for your response xld!

Unfortunately I am getting a "Compile error: Method or data member not found". Any idea how I can get the data from Data Source wb into the Data wb?

I am attaching the two workbooks.Data.xlsxData Source.xlsx

[Bob Phillips]

Try this

Public Sub CopyData()
    Dim wb As Workbook
    Dim filename As String
     
    With Application.FileDialog(msoFileDialogOpen)
         
        .AllowMultiSelect = False
         
        If .Show Then
             
            filename = .SelectedItems(1)
             
            Set wb = Workbooks.Open(filename:=filename)
            wb.Worksheets(1).UsedRange.Copy Workbooks("Data.xlsx").Worksheets("DataSheet").Range("A1")
            wb.Close SaveChanges:=False
            Set wb = Nothing
        End If
    End With
End Sub

[Fredek800]

Hi xld,

The code brings up "File Open" screen (screenshot attached) and doesn't pull the data directly. When I however pick the file it pulls the data!! DO you think the file path is in the wrong format?

Screenshot.JPG

[Bob Phillips]

Yes, I have put a file picker in the code rather than have you hard-coding it into your workbook.

[Fredek800]

Ah ok! Is there any way the VBA can use the hard coded cell as the path?

xld, if you are ever in London I need to buy you beers, many many beers!

[Bob Phillips]

It could, but which workbook/worksheet should it be in? I would still use a file dialog to populate that cell.

Actually, I will probably be in London next week, maybe meeting up with Ken Puls and Andy Pope

[Fredek800]

It could, but which workbook/worksheet should it be in? I would still use a file dialog to populate that cell.

Actually, I will probably be in London next week, maybe meeting up with Ken Puls and Andy Pope

I’d prefer a cell that has a hard coded file path in the Data workbook. This is because i have already used VBA to find the specific file name I am looking for (all data source workbooks use date and time stamp).

Great, let me know! I’ll send you my details via message.

[Bob Phillips]

As I said, where is that cell. which workbook, which worksheet?

[Fredek800]

As I said, where is that cell. which workbook, which worksheet?

Hi xld, I have the folder path in cell A1 of Sheet1 of the Data workbook. It’s the path highlighted in red in the Data workbook attached above.

many thanks for all your help!

[Bob Phillips]

SO maybe this

Public Sub CopyData()
    Dim wb As Workbook
    Dim filename As String
     
    filename = Workbooks("Data.xlsx").Worksheets(1).Range("A1").Value
     
    Set wb = Workbooks.Open(filename:=filename)
    wb.Worksheets(1).UsedRange.Copy Workbooks("Data.xlsx").Worksheets("DataSheet").Range("A1")
    wb.Close SaveChanges:=False
    Set wb = Nothing
End Sub