Copying for another workbook and create a new sheet named "" and pasting there

[drop05]

Issac Guerra <issaconthego@gmail.com>​	2:06 PM (0 minutes ago)
to me

Hello, with below code I ahve it set for the user to select a file to copy from and then it copies the whole sheet and pastes it into the current workbook into a sheet called name 1. Is there a way to have it copy and the create a sheet and paste what is copied into there and name that newly created sheet?


Sub GetData()

Dim DataToCopy As Variant
Dim Data As Workbook

Application.ScreenUpdating = False

DataToCopy = Application.GetOpenFilename(Title:="Select Data File", FileFilter:="Excel Files (*.xlsm*), *xlsm*")

If DataToCopy <> False Then

Set Data = Application.Workbooks.Open(DataToCopy)

Data.Sheets("Data").UsedRange.Copy
ThisWorkbook.Worksheets("Sheet1").Range("A1").PasteSpecial xlPasteValues
ThisWorkbook.Worksheets("Sheet1").Range("A1").PasteSpecial xlPasteFormats
ThisWorkbook.Sheets("File Paths").Range("D5").Value = Data.FullName

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

End If

Application.ScreenUpdating = True

End Sub

 

[mrshl9898]

Sub GetData()

Dim DataToCopy As Variant
Dim Data As Workbook

Application.ScreenUpdating = False

DataToCopy = Application.GetOpenFilename(Title:="Select Data File", FileFilter:="Excel Files (*.xlsm*), *xlsm*")

If DataToCopy <> False Then

Set Data = Application.Workbooks.Open(DataToCopy)

Data.Sheets("Data").UsedRange.Copy
ThisWorkbook.Sheets.Add.Name = "myNewSheet"
ThisWorkbook.Worksheets("myNewSheet").Range("A1").PasteSpecial xlPasteValues
ThisWorkbook.Worksheets("myNewSheet").Range("A1").PasteSpecial xlPasteFormats
ThisWorkbook.Sheets("myNewSheet").Range("D5").Value = Data.FullName

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

End If

Application.ScreenUpdating = True

End Sub