Macro to create a new workbook from a worksheet
Closed
simonrhi
Posts
3
Registration date
Thursday May 18, 2017
Status
Member
Last seen
May 24, 2017
-
May 21, 2017 at 02:27 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - May 24, 2017 at 05:13 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - May 24, 2017 at 05:13 AM
Related:
- Excel macro to create new workbook based on value in cells
- Excel macro to create new sheet based on value in cells - Guide
- Create skype account with gmail - Guide
- Spell number in excel without macro - Guide
- Create new instagram account without phone number - Guide
- Create snapchat account - Guide
2 responses
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
May 23, 2017 at 07:22 AM
May 23, 2017 at 07:22 AM
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
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
May 24, 2017 at 05:13 AM
May 24, 2017 at 05:13 AM
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.
May 24, 2017 at 04:14 AM
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