Related:
- Macro to create new workbook and copy data
- Excel macro create new workbook - Best answers
- Excel macro to create new workbook - Best answers
- Excel - A macro to create new workbook and copy data ✓ - Forum - Excel
- Use a Macro to Create New Workbook and Copy Data in Excel - How-To - Excel
- Excel - A macro to create new workbook and copy data - How-To - Excel
- Macro to create new workbook and copy data ✓ - Forum - Excel
- Macro to create new sheet and copy data - Forum - Excel
2 replies
vcoolio
- Posts
- 1260
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- February 3, 2020
Hello Simon,
Assuming that you have a name in, say, cell A1 with which you wish to name the new work book, the following code may do the task for you:-
The code will create a new work book, name it from cell A1 and copy all data from the active sheet in the current work book to the first sheet in the new work book.
You may have to change line 6 to the sheet that you are creating the new work book from.
In line 10, you will need to add the file path that you need to save the new work book to.
If the new file name is in another cell, just change the reference to cell A1 in line 10 above.
I hope that this helps.
Cheerio,
vcoolio.
Assuming that you have a name in, say, cell A1 with which you wish to name the new work book, the following code may do the task for you:-
Sub CreateNewWbk()
Dim rng As Range
Dim ws As Worksheet
Dim nFN As String
Set ws = Sheet1
Application.ScreenUpdating = False
nFN = "C:\Users\YOUR FILEPATH HERE\" & Range("A1").Value & ".xlsx"
With ws
Set rng = .UsedRange
rng.Copy
Workbooks.Add
Sheets("Sheet1").[A1].PasteSpecial xlPasteValues
Sheets("Sheet1").[A1].PasteSpecial xlPasteFormats
Sheets("Sheet1").[A1].Select
ActiveWorkbook.SaveAs Filename:=nFN
ActiveWorkbook.Close
End With
MsgBox "Done!", vbExclamation
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
The code will create a new work book, name it from cell A1 and copy all data from the active sheet in the current work book to the first sheet in the new work book.
You may have to change line 6 to the sheet that you are creating the new work book from.
In line 10, you will need to add the file path that you need to save the new work book to.
If the new file name is in another cell, just change the reference to cell A1 in line 10 above.
I hope that this helps.
Cheerio,
vcoolio.
vcoolio
- Posts
- 1260
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- February 3, 2020
Hello Simon,
Yes, no worries.
Just add the following line:-
directly after line 16 in the code above.
Let us know if that sorts it out for you.
Cheerio,
vcoolio.
Yes, no worries.
Just add the following line:-
Sheets("Sheet1").[A1].PasteSpecial xlPasteColumnWidths
directly after line 16 in the code above.
Let us know if that sorts it out for you.
Cheerio,
vcoolio.
Subscribe To Our Newsletter!
Thanks for this, it works really well. One thing i would like to change if possible and that is to retain the column widths from the original worksheet, do you think this possible?
Many thanks for your assistance with this.
Best regards,
Simon