| 2:06 PM (0 minutes ago) | |||
|
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