Copy table from another excel file

Hi,

I'm trying to create a macro for some guys at work who have very little knowledge of excel, so it needs to be really simple to use.

Basically the user will be working on a workbook, on a specific sheet and I want them to press a button, where they choose a specific file and then the macro automatically copies set cells (always the same cells) from a set tab (name will not change) to the original workbook in a set location.

I've managed to do most of it but I'm having proplems making the original workbook active using the code below:

Windows("Enhanced Calculator Template v1.6.xls").Activate

My problem is that the filename of the original excel workbook will change and be different for each user. Ideally I want a way of creating a string where I can store the filename of the original excel workbook.

Thanks for all your help.

Hello Carim,
I would like to know how almost everyone uses Dim ... as integer or as string at the beginning of each macro and what does it mean for both??? I tried to find out explanations but I could not.
Thank you

Thank you for the link, now i understand!
cheers

Carim,

Thanks for the pragmatic solution!

Still got problems...

I'm getting the following error message around the Windows(myfile).Activate line (note that the cell it's referencing has the .xls in value)... this is the cell formula I used:

=RIGHT(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),FIND("]",CELL("filename"))-1-FIND("[",CELL("filename")))

Here's the code I have:

Public Sub ImportData()


'Define variables
Dim sourceRange As Range
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant
Dim myfile As String


myfile = Range("o25").Value

'Go to Import Temp tab
Sheets("Import Temp").Select

Range("B4").Select

SaveDriveDir = CurDir
MyPath = "C:"
ChDrive MyPath
ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _
MultiSelect:=False)


Workbooks.Open Filename:=FName
Sheets("Import Temp").Select

Range("A2:P31").Select
Selection.Copy

Windows(myfile).Activate

Sheets("Import Temp").Select

Range("B4").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub