How to copy master sheet as values and automatically set new name
Category: [Excel] Demo Available
How to copy master sheet as values and automatically set new name
Requirement:
The user has looked for VBA on the Internet that copies the data from the master sheet to a new sheet of the same workbook but does not fit my needs.
The user is looking for someone to edit this VBA code below and customize the conditions required.
The user needs Master sheet template copy to new sheet as values without formulas, color cells, comments and automatically set new name from cell.
Some cells are merged, also contain comments and formulas.
Conditions:
1. Copy the template Master sheet immediately after the sheet Master in same workbook
2. Read data from cell Z5 and set as the new Sheet name (format dd.mm.yy)
3. Paste multiple print area (do not paste other data from Sheet)
4. Paste data as values/text without formulas, comments and cell color (format cells need be copied)
5. If there is already a sheet with the name, display warning and cancel copy
6. If the user cancels the copy, exit from VBA and do not copy nothing
The user is using this VBA code:
Code:
Sub CopyMasterAsValuesAndSetNewName()
Dim sName As String
Dim wks As Worksheet
Worksheets("Master").Copy after:=Sheets(1)
Set wks = ActiveSheet
Do While sName <> wks.Name
sName = Application.InputBox _
(Prompt:="Write new sheet name")
On Error Resume Next
wks.Name = sName
On Error GoTo 0
Loop
Set wks = Nothing
End Sub
Solution:
Code:
Option Explicit
Sub CopyMasterAsValuesAndSetNewName()
Application.ScreenUpdating = 0
Sheets("Master").Copy , Sheets("Master")
With ActiveSheet
On Error GoTo GetOut
.Name = Format([z5], "dd.mm.yy")
On Error GoTo -1: On Error GoTo 0
.Columns(34).Resize(, 100).Delete
With .UsedRange
.Interior.Color = xlNone
.ClearComments
.Validation.Delete
.Value = .Value
End With
Application.Goto .[a1]
ActiveWindow.DisplayGridlines = 0
End With
Exit Sub
GetOut:
MsgBox "A sheet named """ & Format([z5], "dd.mm.yy") & """ already exists.", , "Sheet Exists"
Application.DisplayAlerts = 0
Sheets(2).Delete
Application.DisplayAlerts = 1
Application.Goto Sheets("Master").[a1]
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by KjBox.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to paste value when creating a master summary sheet |
| How to use a macro to copy data from multiple workbooks to one master sheet in another workbook |
| How to list & display all files in user folder, select file and copy specific tab into master sheet |
| How to copy from multiple workbooks to master file and keep overwrite data |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.