VBA; Copy a whole work sheet from another workbook and have it paste into a sheet in current workbook

[drop05]

Hello, I am trying to copy a whole worksheet from another workbook that the user can select and have it paste into a sheet in my workbook that my code is in. However with this code I am using it keeps opening a new workbook and pasting it there with the name of the sheet instead it is copied and also pasting my code in the sheet where i want to have the sheet i wanted copied from into there and thats not what i want.

Here is my code:

Sub GetData()

Dim FileToOpen As Variant
Dim OpenBook As Workbook

Application.ScreenUpdating = False


FileToOpen = Application.GetOpenFilename(Title:="Select Workbook", FileFilter:="Excel Files (*.xlsm*), *xlsm*")
'& "Excel Files(*xlsm*), *xlsm*")
If FileToOpen <> False Then

Set OpenBook = Application.Workbooks.Open(FileToOpen)

OpenBook.Sheets("Details").Copy
ThisWorkbook.Worksheets("Copy of Detail").PasteSpecial xlPasteValues

OpenBook.Close False
Else
MsgBox "No file was selected!"


End If

Application.ScreenUpdating = True


End Sub

trying to get it to copy from details sheet from the selected workbook the user picks and then paste into the workbook my code is in into a tab called, copy of detail. Not have it open a whole new workbook and paste it into a sheet in there calling it still details.

Thank you for any help!

 

[drop05]

Where?

Kind like this, on the sheet where the user can push the buttons have the file path display next to button, there is two because i have the same macro but the user selects another file

 

[Fluff]

An image does does tell where the path should be put, nor the name of the sheet.

 

[drop05]

An image does does tell where the path should be put, nor the name of the sheet.

the button will run the marco for the user to select the file as the code above does, then in F3 on file path tab would want the name of the file they selected the file path text should go in there, if that is possible. If not i may leave that out, was trying to be a little fancy is all

 

[Fluff]

Thanks for that, how about

Sheets("FilePaths").Range("F3").Value = OpenBook.Path

 

[drop05]

Thanks for that, how about

Sheets("FilePaths").Range("F3").Value = OpenBook.Path

I tried it and got a run time error '9' Subscript out of range when i added that line and the error is on that line. Would i need to add ThisWorkbook.Worksheets to the line?

 

[Fluff]

Just add Thisworkbook. to it.

 

[drop05]

Thanks for that, how about

Sheets("FilePaths").Range("F3").Value = OpenBook.Path

i did it as ThisWorkbook.Sheets("FilePaths").Range("F3").Value = OpenBook.Path
and it worked, however it showed the desktop, which is where i selected the example file, but it just showed user\Desktop which is the file path, any way to include the name of the file after?
ex:

user\Desktop\Examplefile.xlsm

 

[Fluff]

Use FullName rather than Path

 

[drop05]

Use FullName rather than Path

You are the best I cannot thank you enough! I am loving this, language is a lot different but being in excel seems good when working with other excel files, thank you so much again!

 

[Fluff]

You're welcome & thanks for the feedback.