Previous Topic Next Topic

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 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 - May 24, 2017 at 05:13 AM
Hello,

Please can somebody help me with the below query?

I would like to create a macro which copies all of the data and formatting from a specific worksheet into a new workbook with pasted values.

I would be very grateful to receive any assistance with this matter.

Thanks for your time.

Best wishes
Simon
Related:

2 replies

Answer 1 / 2
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
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:- 
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.
0
simonrhi Posts 3 Registration date Thursday May 18, 2017 Status Member Last seen May 24, 2017
May 24, 2017 at 04:14 AM
Hi Vcoolio,

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
0
Answer 2 / 2
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
May 24, 2017 at 05:13 AM
Hello Simon,

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.
0