CODEWITHSUNDEEP
excelvbacopypaste
Roberto
Roberto

Reputation: 21

Create and copy new sheet

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

Upvotes: 0

Views: 82

Answers (2)

FunThomas
FunThomas

Reputation: 29592

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

Upvotes: 1

Nick
Nick

Reputation: 687

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!

Upvotes: 0

Related Questions