Create and copy new sheet

Question

Newbie here, I have a template sheet with a table containing formulas.At the end I want to press a button to create and copy-paste values from template sheet to the new sheet so here is what i got:

Sub CreateSheet() Dim xName As String Dim xSht As Object Dim xNWS As Worksheet On Error Resume Next xName = Application.InputBox("Please enter a name for this new sheet ", "New Sheet") If xName = "" Then Exit Sub Set xSht = Sheets(xName) If Not xSht Is Nothing Then MsgBox "Sheet cannot be created as there is already a worksheet with the same name in this workbook" Exit Sub End If ActiveSheet.Copy after:=Sheets(Sheets.Count) Set xNWS = Sheets(Sheets.Count) xNWS.Name = xName End Sub

Problem is I just want to Paste Values without any formulas from template sheet

Thanks

FunThomas · Accepted Answer · 2019-03-22 14:16:48Z

You could go for a solution that would create an empty sheet manually and use PasteSpecial with option xlPasteValuesto copy only values - but this would not copy the formatting. You could then use another PasteSpecial to copy the formatting.

An alternative is to first copy the whole template as you do, including formulas, and add the following statement. It looks odd at first, but it converts all formulas into values.

xNWS.UsedRange.Value2 = xNWS.UsedRange.Value2

yes ,it worked ,although it also copied the command buttons from the template sheet

Nick · Accepted Answer · 2019-03-22 14:12:56Z

what you are looking for is the Range.PasteSpecial method.

full expression is:
PasteSpecial( _Paste_ , _Operation_ , _SkipBlanks_ , _Transpose_ )

see https://learn.microsoft.com/en-us/office/vba/api/excel.range.pastespecial

As of right now you are pasting the entire sheet which is quite ineffective. I suggest familiarising yourself with Listobject(s), to define your table in VBA and then use Range("YourTable").PasteSpecial _ Paste:=xlPasteValues to copy only the values in your table to the new sheet.

here is a quick sample:

Private Sub Sample() Dim tbl As ListObject Set tbl =Sheets("YourSheet").Listobjects("YourTable") 'note the "s" in ListObjects End Sub

To refer to your table range use Range("TableName").ListObject.
See https://www.dingbatdata.com/2017/11/24/referencing-listobjects-with-vba for more information

Hope this was helpful, have a nice day!

Collectives™ on Stack Overflow

Create and copy new sheet

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related