Need macro to copy from another book

[Russk68]

Hi
Using a macro, I need to copy column A from a sheet in another book in the same folder as my current book and paste to column A. I would like not to have to open the book to do this. I assume that the book name and sheet name would have to always be the same; such as Book1, Sheet1. I would then create a button to assign the macro to.
TIA!

 

[Zot]

Should be something like this

Sub ImportData()

Dim FName As String, FPath As String
Dim strColMaster As String, strColSource As String
Dim rngCopy As Range
Dim wsMaster As Worksheet, wsSource As Worksheet
Dim wbMaster As Workbook, wbSource As Workbook

Set wbMaster = ActiveWorkbook
Set wsMaster = wbMaster.Sheets("Sheet1")                  ' Change Master sheet name as reuired

FPath = wbMaster.Path
FName = "Book1.xlsx"                                                 ' Put the Source file name here

Application.ScreenUpdating = False

Set wbSource = Workbooks.Open(Filename:=FPath & "\" & FName, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set wsSource = wbSource.Sheets("Sheet1")                    ' Change Source sheet name as required

Set rngCopy = wsSource.Range("A1", wsSource.Cells(Rows.Count, "A").End(xlUp))

rngCopy.Copy wsMaster.Range("A1")                           ' Paste copied range to A1 in wsMaster

wbSource.Close False                                                  ' Close wbSource without saving

End Sub

 

[Russk68]

Hi Zot
I'm getting this error. The path looks good to me.
Thank you for your help!

 

[Russk68]

More clear images

 

[Zot]

I think backslash mistake here
Set wbSource = Workbooks.Open(Filename:=FPath & "\" & FName, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)

 

[Russk68]

I changed it to "/" and I don't get an error now. I see something happen but column A remains blank.
Heres. a screenshot from Book1, Sheet1.
Unfortunately I have to reduce the file size to upload and now it's unreadable.
Column A has information

 

[Zot]

I have tested the code with mock-up source Book1.xlsx and no problem to copy data.

If everything is correct like workbook name and sheet name in the source and the import destination sheet name is also Sheet1, then I have no idea. I could hardly see the screenshot, even with microscope ?

I'm not sure if it is something to do with Mac. I just realized that you are on Mac.

 

[Russk68]

I have tested the code with mock-up source Book1.xlsx and no problem to copy data.

If everything is correct like workbook name and sheet name in the source and the import destination sheet name is also Sheet1, then I have no idea. I could hardly see the screenshot, even with microscope ?

I'm not sure if it is something to do with Mac. I just realized that you are on Mac.

I'll try it on Windows and let you know. Thanks for your time!

 

[johnnyL]

I thought the goal was to not open the workbook?

 

[Russk68]

I thought the goal was to not open the workbook?

That is correct. I was just showing that there was information in column A. I have not tried on Windows yet.