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? : pray2:: pray2:: pray2:: pray2:: pray2:

[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.2045120452

[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?

20453

[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! :D

[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