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!

 

[Fluff]

Whereabouts do you want to paste the data? Is it starting in A1

 

[drop05]

Whereabouts do you want to paste the data? Is it starting in A1

i want the data pasted exactly how it is from the sheet i am copying so yeah a1 should be a good starting point
Copy from A1 to whole sheet
Paste into a1 to whole sheet

 

[Fluff]

In that case try

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

 

[drop05]

In that case try

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

That did the trick! You are awesome! Another quick questions, it pasted the values, is there a way I can do the format, like if i have colored cells, or cells with drop downs
to paste over as well

 

[Fluff]

Easiest option would be to copy the entire sheet to the other workbook.

 

[drop05]

Easiest option would be to copy the entire sheet to the other workbook.

This is true, however the reason I ask is because I have a macro that will add more columns if the user needs in that workbook copying from, and it has like a drop down of states and such.

 

[Fluff]

In that case how about

OpenBook.Sheets("Details").UsedRange.Copy ThisWorkbook.Worksheets("Copy of Detail").Range("A1")

 

[drop05]

In that case how about

OpenBook.Sheets("Details").UsedRange.Copy ThisWorkbook.Worksheets("Copy of Detail").Range("A1")

This did the trick, i created a button that the user can push and it run that code, is there a way to have it to display the file path selected on that sheet?

 

[Fluff]

Where?