For some of my reports, I needed to share some useful information to my customers, the idea was to give them some general information and some comments. So what I wanted it is to copy one of the sheets of my report to a new excel file and into this new one, create a sheet to provide them some feedback.

When I use the macro ?

To create a new excel file to share only some information to other people.

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

How is the macro ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.

New file in single sheet:

Sub test()
Dim FName As String
Dim FPath As String
Dim NewBook As Workbook
' change fullpath by the full path where to save the new file
FPath = "\\fullpath\"
' change filename by the name of your new file
FName = "filename" & ".xlsx"
Set NewBook = Workbooks.Add
' start single sheet
' change Sheet1 by the name of the sheet to copy
' change A1:D20 by the cell section to copy
ThisWorkbook.Sheets("sheet1").Range("A1:D20").Copy
' change A1 by the cell where to paste
NewBook.Sheets(1).Range("A1").PasteSpecial (xlPasteValues)
NewBook.Sheets(1).Range("A1").PasteSpecial (xlPasteFormats)
NewBook.Sheets(1).Range("A1").PasteSpecial (xlPasteColumnWidths)
' end single sheet
Application.DisplayAlerts = False
NewBook.SaveAs Filename:=FPath & FName, FileFormat:=51
NewBook.Close SaveChanges:=True
End Sub

New file in multiple sheets:

Sub test()
' start multiple sheets change section start single sheet - end single sheet by this section
With NewBook.Sheets
' create first new sheet with name test2 to paste data, change the name if you want
.Add().Name = "test2"
' create second new sheet with name test1 to explain the content, change the name if you want
.Add().Name = "test1"
End With
' change Sheet1 by the name of the sheet to copy
' change A1:D20 by the cell section to copy
ThisWorkbook.Sheets("Sheet1").Range("A1:D20").Copy
' change test2 by the same name of the second new sheet where to paste
' change A1 by the cell where to paste
NewBook.Sheets("test2").Range("A1").PasteSpecial (xlPasteValues)
NewBook.Sheets("test2").Range("A1").PasteSpecial (xlPasteFormats)
NewBook.Sheets("test2").Range("A1").PasteSpecial (xlPasteColumnWidths)
' change test1 by the same name of the first new sheet
' change A1 by the cell where to put your comment so change write everything you want by what you want
NewBook.Sheets("test1").Range("A1").Value = "write everything you want"
' change test1 by the same name of the first new sheet, the sheet to display when open the new file
NewBook.Sheets("test1").Select
' end multiple sheets change section start single sheet - end single sheet by this section
End Sub