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

drop05

Board Regular
Joined
Mar 23, 2021
Messages
113
Office Version
  1. 365
Platform
  1. Windows
AOh14GgsqOHzMK_Hoy4Y7dyFCxlLrLU_iEDKxHGrKJmoYw=s40

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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
VBA Code:
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
 

Forum statistics

Threads
1,141,204
Messages
5,704,949
Members
421,372
Latest member
Jamie11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top